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

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 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);