[jboss-jira] [JBoss JIRA] Created: (JBPORTAL-2257) query tuning

[Prabhat Jha (JIRA)]

query tuning
------------

                 Key: JBPORTAL-2257
                 URL: https://jira.jboss.org/jira/browse/JBPORTAL-2257
             Project: JBoss Portal
          Issue Type: Feature Request
      Security Level: Public (Everyone can see)
          Components: Portal Core
    Affects Versions: 2.7.0 Final, 2.6.7 Final
            Reporter: Prabhat Jha
            Assignee: Prabhat Jha
             Fix For: 2.7.1 Final, 2.6.8 Final


See http://www.ludovicocaldara.net/dba/jboss-portal-and-mysql-scalability/

Currently, following query is genrerated:

SELECT PATH, NAME FROM JBP_OBJECT_NODE  WHERE PK IN (
SELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'LUDOVICO'
AND ju.jbp_enabled = 1));

Should it be tuned to:

SELECT DISTINCT a.PATH, a.NAME /* , b.NODE_KEY */ FROM  JBP_OBJECT_NODE
a, JBP_OBJECT_NODE_SEC b
WHERE a.pk=b.NODE_KEY
AND b.ROLE IN (
SELECT jr.jbp_name FROM jbp_users ju, jbp_role_membership jrm,
jbp_roles jr
WHERE jrm.jbp_uid = ju.jbp_uid
AND jr.jbp_rid = jrm.jbp_rid
AND ju.jbp_uname = 'UTDEMO'
AND ju.jbp_enabled = 1);



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira