You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@roller.apache.org by "Kohei Nozaki (JIRA)" <ji...@apache.org> on 2016/01/09 03:10:39 UTC
[jira] [Commented] (ROL-2092) Optimizing the query that loads the
comments in the Admin UI
[ https://issues.apache.org/jira/browse/ROL-2092?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15090340#comment-15090340 ]
Kohei Nozaki commented on ROL-2092:
-----------------------------------
I've confirmed that {{create index co_posttime_idx on roller_comment( posttime );}} works very well for global comment management page, but unfortunately not for a weblog specific one on MySQL. My experiments on MySQL 5.6.27 below:
{noformat}
mysql> explain SELECT id AS a1, content AS a2, contenttype AS a3, email AS a4, name AS a5, notify AS a6, plugins AS a7, posttime AS a8, referrer AS a9, remotehost AS a10, status AS a11, url AS a12, useragent AS a13, entryid AS a14 FROM roller_comment ORDER BY posttime DESC LIMIT 31 OFFSET 0;
+----+-------------+----------------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | roller_comment | ALL | NULL | NULL | NULL | NULL | 173033 | Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+--------+----------------+
1 row in set (0.00 sec)
mysql> create index co_posttime_idx on roller_comment( posttime ); Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain SELECT id AS a1, content AS a2, contenttype AS a3, email AS a4, name AS a5, notify AS a6, plugins AS a7, posttime AS a8, referrer AS a9, remotehost AS a10, status AS a11, url AS a12, useragent AS a13, entryid AS a14 FROM roller_comment ORDER BY posttime DESC LIMIT 31 OFFSET 0;
+----+-------------+----------------+-------+---------------+-----------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+-----------------+---------+------+------+-------+
| 1 | SIMPLE | roller_comment | index | NULL | co_posttime_idx | 7 | NULL | 31 | NULL |
+----+-------------+----------------+-------+---------------+-----------------+---------+------+------+-------+
1 row in set (0.00 sec)
{noformat}
On PostgreSQL, the index improves performance of both queries.
May we need to use separate two queries to avoid joining for further optimization of a weblog specific one. Any feedback from who familiar with MySQL would be greatly appreciated...
> 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: 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_y2bdtpg@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)