I found several queries running on a MySQL 5.0 database like this one:<\/p>\n
SELECT PATH, NAME FROM JBP_OBJECT_NODE\u00a0 WHERE PK IN (\r\nSELECT NODE_KEY FROM JBP_OBJECT_NODE_SEC WHERE ROLE IN (\r\nSELECT jr.jbp_name from jbp_users ju, jbp_role_membership jrm,\r\njbp_roles jr\r\nwhere jrm.jbp_uid = ju.jbp_uid\r\nand jr.jbp_rid = jrm.jbp_rid\r\nand ju.jbp_uname = 'LUDOVICO'\r\nand ju.jbp_enabled = 1));\r\n<\/pre>\nThis query is related to JBoss Portal and does a full scan on table JBP_OBJECT_NODE.<\/p>\n
It has bad performances (>0.8 sec) with just a few records:<\/p>\n
mysql> select count(*) from JBP_OBJECT_NODE;
\n+———-+
\n| count(*) |
\n+———-+
\n|\u00a0\u00a0\u00a0 33461 |
\n+———-+<\/p>\n
If I rewrite the query using an inner join (\u00e0 la Oracle, please forgive me) instead of a subquery I get an index scan:<\/p>\n
SELECT distinct a.PATH, a.NAME \/* , b.NODE_KEY *\/ from\u00a0 JBP_OBJECT_NODE\r\na, JBP_OBJECT_NODE_SEC b\r\nwhere a.pk=b.NODE_KEY\r\nand b.ROLE IN (\r\nSELECT jr.jbp_name from jbp_users ju, jbp_role_membership jrm,\r\njbp_roles jr\r\nwhere jrm.jbp_uid = ju.jbp_uid\r\nand jr.jbp_rid = jrm.jbp_rid\r\nand ju.jbp_uname = 'UTDEMO'\r\nand ju.jbp_enabled = 1);\r\n<\/pre>\nWith 30k records the execution time falls down from 0.8 secs to 0.01 secs…
\nThat’s NOT all! I found this open bug:<\/p>\n
https:\/\/jira.jboss.org\/jira\/browse\/JBPORTAL-2040<\/p>\n
With many users registered in, the JBoss Portal Admin console tooks over a minute to show a single page…<\/p>\n
I don’t like portals…<\/p>\n","protected":false},"excerpt":{"rendered":"
I found several queries running on a MySQL 5.0 database like this one: SELECT PATH, NAME FROM JBP_OBJECT_NODE\u00a0 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 … Continue reading