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

[Ludovico Caldara (JIRA)]

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

Ludovico Caldara commented on JBPORTAL-2257:
--------------------------------------------

Hi, I regret, I'm not a programmer and I do not follow Portal development. I'm a DBA and I saw these queries running in mysql. This is a customer portal running Portal 2.6.1-GA.
The customer didn't permit to analyze his source code but say these queries are generated by Jboss Portal while accessing dashboard within their application. (I care about they could be wrong...)

I'll check more slow queries to see if something can be improved.

kind regards!
-- 
Ludovico

> 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