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

[Prabhat Jha (JIRA)]

    [ https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12442361#action_12442361 ] 

Prabhat Jha commented on JBPORTAL-2257:
---------------------------------------

To be more precise, the attached log after-user-login-mysql.log is once user logs in and then clicks on dashboard.

> 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.6.7 Final, 2.7.0 Final
>            Reporter: Prabhat Jha
>            Assignee: Chris Laprun
>             Fix For: 2.7.1 Final, 2.6.8 Final
>
>         Attachments: after-user-login-mysql.log
>
>
> 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