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

[Chris Laprun (JIRA)]

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

Chris Laprun commented on JBPORTAL-2257:
----------------------------------------

The query should show up when permissions are checked against an instance.
Maybe we should consider asking the person who reported the issue what path he followed to run into the problem?

> 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