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