I found several queries running on a MySQL 5.0 database like this one:
|
1 2 3 4 5 6 7 8 |
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)); |
This query is related to JBoss Portal and does a full scan on table JBP_OBJECT_NODE.
It has bad performances (>0.8 sec) with just a few records:
mysql> select count(*) from JBP_OBJECT_NODE;
+———-+
| count(*) |
+———-+
| 33461 |
+———-+
If I rewrite the query using an inner join (à la Oracle, please forgive me) instead of a subquery I get an index scan:
|
1 2 3 4 5 6 7 8 9 10 |
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); |
With 30k records the execution time falls down from 0.8 secs to 0.01 secs…
That’s NOT all! I found this open bug:
https://jira.jboss.org/jira/browse/JBPORTAL-2040
With many users registered in, the JBoss Portal Admin console tooks over a minute to show a single page…
I don’t like portals…