[jboss-jira] [JBoss JIRA] Commented: (JBPORTAL-2257) query tuning
[
https://jira.jboss.org/jira/browse/JBPORTAL-2257?page=com.atlassian.jira....
]
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