[
https://issues.apache.org/jira/browse/ROL-2092?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15090375#comment-15090375
]
Kohei Nozaki commented on ROL-2092:
-----------------------------------
I've found that the MySQL specific clause named {{STRAIGHT_JOIN}} improves the
performance.
The problematic query which doesn't use {{STRAIGHT_JOIN}}:
{noformat}
mysql> explain SELECT t0.id AS a1, t0.content AS a2, t0.contenttype AS a3,
t0.email AS a4, t0.name AS a5, t0.notify AS a6, t0.plugins AS a7, t0.posttime
AS a8, t0.referrer AS a9, t0.remotehost AS a10, t0.status AS a11, t0.url AS
a12, t0.useragent AS a13, t0.entryid AS a14 FROM roller_comment t0, weblogentry
t1 WHERE ((t1.websiteid = 'f0588427-f2ca-4843-ac87-bbb31aa6013c') AND (t1.id =
t0.entryid)) ORDER BY t0.posttime DESC LIMIT 31 OFFSET 0;
+----+-------------+-------+------+---------------------------------------+-----------------+---------+------------+-------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+-------+------+---------------------------------------+-----------------+---------+------------+-------+-----------------------------------------------------------+
| 1 | SIMPLE | t1 | ref | PRIMARY,we_weblogid_idx,we_combo2_idx |
we_weblogid_idx | 50 | const | 1 | Using where; Using index;
Using temporary; Using filesort |
| 1 | SIMPLE | t0 | ref | co_entryid_idx |
co_entryid_idx | 50 | test.t1.id | 86516 | NULL
|
+----+-------------+-------+------+---------------------------------------+-----------------+---------+------------+-------+-----------------------------------------------------------+
2 rows in set (0.00 sec)
{noformat}
With {{STRAIGHT_JOIN}}:
{noformat}
mysql> explain SELECT STRAIGHT_JOIN t0.id AS a1, t0.content AS a2,
t0.contenttype AS a3, t0.email AS a4, t0.name AS a5, t0.notify AS a6,
t0.plugins AS a7, t0.posttime AS a8, t0.referrer AS a9, t0.remotehost AS a10,
t0.status AS a11, t0.url AS a12, t0.useragent AS a13, t0.entryid AS a14 FROM
roller_comment t0, weblogentry t1 WHERE ((t1.websiteid =
'f0588427-f2ca-4843-ac87-bbb31aa6013c') AND (t1.id = t0.entryid)) ORDER BY
t0.posttime DESC LIMIT 31 OFFSET 0;
+----+-------------+-------+--------+---------------------------------------+-----------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------------+-----------------+---------+-----------------+------+-------------+
| 1 | SIMPLE | t0 | index | co_entryid_idx |
co_posttime_idx | 7 | NULL | 31 | NULL |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY,we_weblogid_idx,we_combo2_idx |
PRIMARY | 50 | test.t0.entryid | 1 | Using where |
+----+-------------+-------+--------+---------------------------------------+-----------------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)
{noformat}
If this could be a proper solution, we may want to implement it as MySQL
specific option. I guess it would be reasonable because vast majority of Roller
users are using MySQL. How do you think?
> Optimizing the query that loads the comments in the Admin UI
> ------------------------------------------------------------
>
> Key: ROL-2092
> URL: https://issues.apache.org/jira/browse/ROL-2092
> Project: Apache Roller
> Issue Type: Improvement
> Components: Data Model & JPA Backend
> Affects Versions: 5.1.2
> Reporter: Kohei Nozaki
> Assignee: Roller Unassigned
> Priority: Minor
> Attachments: ROL-2092.patch, roller-slowquery.png
>
>
> Listing comments in the admin UI seems useless due to terribly slow query in
> a Roller instance which has over ten thousands comments. Originally reported
> by Matt:
> http://mail-archives.apache.org/mod_mbox/roller-dev/201503.mbox/%3ccafhcnt4hth01ghkntthikpf3allfyt1urqvzxekm8a_y2bd...@mail.gmail.com%3e
> I think many unnecessary joins happen in it, So I believe it can be optimized
> a way without altering any of existing roller tables (I guess using {{NEW}}
> clause in the JPQL might help...).
> Anyway I wish I could see the result of {{EXPLAIN}} for the query...
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
