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…
The following two tabs change content below.
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
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
Hi,
Which version of JBoss Portal are you using? Can you please describe what user interaction generates above query? I enabled query logging on MySql 5 and I did not see any query with “IN” in where clause. If I can reproduce what you are seeing then we can certainly test it with your optimization. I appreciate your work. Please feel free to comment on https://jira.jboss.org/jira/browse/JBPORTAL-2257 and hopefully we will also be able to fix https://jira.jboss.org/jira/browse/JBPORTAL-2040. 🙂