Inefficient latest posts query

[reloadgg]

The following query was taking ~8 seconds with default indexes;

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 

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);

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 |

This on the otherhand runs in ~0.01 seconds.

[axew3]

Great! Going to patch when in place to commit, as soon is possible.
There are lot of patches to apply on code, so since the valuable note above, when and if you can please follow with your precious suggestions.
Thank You, awesome!

[UPDATED 22 AUG 2018]
1.9.1 has been released
Not only queries on the private static function last_forums_topics of the file class.wp.w3all-phpbb.php have been rewrite to be more clear and efficient. Thank you again.