Inefficient latest posts query
Posted: Thu Aug 16, 2018 11:06 am
The following query was taking ~8 seconds with default indexes;
The problem is the "group-by P.topic" clause, which isn't really something that is well covered by the index. And this shows by scanning ~48k out of 90k rows requiring at least a join per row.
Reworking (use of joins makes easier IMO to spot how the join logic is working);
This on the otherhand runs in ~0.01 seconds.
Code: Select all
explain
SELECT DISTINCT T.*, P.*, U.*
FROM phpbb3_topics AS T, phpbb3_posts AS P, phpbb3_users AS U
WHERE T.topic_visibility = 1 AND
T.forum_id NOT IN('14','179','103','104','192','54','178','82','238','327') AND
T.topic_last_post_id = P.post_id AND
P.post_visibility = 1 AND
P.forum_id IN(72,3,2,171,207,7,34,85,13,14,209,27,83,247,63,84,69,70,73,74,75,156,179,82,208,159,88,240,108,153,96,104,102,103,114,120,121,122,123,249,128,129,130,158,173,291,182,278,143,155,146,147,270,261,175,176,178,183,185,259,213,192,194,195,199,263,0,295,276,264,266,215,216,218,219,220,221,222,233,234,272,238,241,242,246,250,251,252,292,289,290,267,284,279,296,293,301,305,306,308,312,313,314,315,316,317,318,319,323,324,325,327,328,54,329,330,20,332,334,335,337) AND
U.user_id = T.topic_last_poster_id
GROUP BY P.topic_id
ORDER BY T.topic_last_post_time
DESC LIMIT 0,10;
Code: Select all
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------------------+------------------+---------+------------------------------------+-------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | T | NULL | ref | forum_id,forum_id_type,fid_time_moved,topic_visibility,forum_vis_last,latest_topics | topic_visibility | 1 | const | 48057 | 99.22 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | U | NULL | eq_ref | PRIMARY | PRIMARY | 4 | reload.T.topic_last_poster_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | P | NULL | eq_ref | PRIMARY,forum_id,topic_id,tid_post_time,post_visibility | PRIMARY | 4 | reload.T.topic_last_post_id | 1 | 50.00 | Using where
Reworking (use of joins makes easier IMO to spot how the join logic is working);
Code: Select all
explain
SELECT T.*, P.*, U.*
FROM phpbb3_topics AS T
join phpbb3_posts AS P on (T.topic_last_post_id = P.post_id and T.forum_id = P.forum_id)
join phpbb3_users AS U on U.user_id = T.topic_last_poster_id
WHERE T.topic_visibility = 1
AND T.forum_id NOT IN('14','179','103','104','192','54','178','82','238','327')
AND T.forum_id IN(72,3,2,171,207,7,34,85,13,14,209,27,83,247,63,84,69,70,73,74,75,156,179,82,208,159,88,240,108,153,96,104,102,103,114,120,121,122,123,249,128,129,130,158,173,291,182,278,143,155,146,147,270,261,175,176,178,183,185,259,213,192,194,195,199,263,0,295,276,264,266,215,216,218,219,220,221,222,233,234,272,238,241,242,246,250,251,252,292,289,290,267,284,279,296,293,301,305,306,308,312,313,314,315,316,317,318,319,323,324,325,327,328,54,329,330,20,332,334,335,337)
AND P.post_visibility = 1
ORDER BY T.topic_last_post_time DESC
LIMIT 0,10 ;
Code: Select all
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------------------+----------------+---------+------------------------------------+------+----------+-------------+
| 1 | SIMPLE | T | NULL | index | forum_id,forum_id_type,fid_time_moved,topic_visibility,forum_vis_last,latest_topics | last_post_time | 4 | NULL | 40 | 38.80 | Using where |
| 1 | SIMPLE | U | NULL | eq_ref | PRIMARY | PRIMARY | 4 | reload.T.topic_last_poster_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | P | NULL | eq_ref | PRIMARY,post_visibility | PRIMARY | 4 | reload.T.topic_last_post_id | 1 | 50.00 | Using where |