Fixed "XFMG: Latest comments" widget causes slow query on large gallery

Affected version
2.1.6

nocte

Well-known member
After upgrading from XF1.5 (with XFMG) to XF2.1 we had a "latest comment" widget on the Gallery index, that caused a slow query. In fact it was that slow, that it caused a timeout.

I assume this widget is only present by default after an upgrade from XF1 to XF2.

To avoid the slow queries, I'd suggest to include a max age limit to for the "latest comments" widget and set it to a few days by default, because otherwise there seems to be a full table scan:

SQL:
SELECT `xf_mg_comment`.*, `xf_mg_album_Album_1`.*, `xf_mg_media_item_Media_2`.*, `xf_mg_category_Category_3`.*, `xf_mg_album_Album_4`.*, `xf_mg_category_Category_5`.*, `xf_mg_rating_Rating_6`.*
                        FROM `xf_mg_comment`
                        LEFT JOIN `xf_mg_album` AS `xf_mg_album_Album_1` ON (`xf_mg_comment`.`content_type` = 'xfmg_album' AND `xf_mg_album_Album_1`.`album_id` = `xf_mg_comment`.`content_id`)
LEFT JOIN `xf_mg_media_item` AS `xf_mg_media_item_Media_2` ON (`xf_mg_comment`.`content_type` = 'xfmg_media' AND `xf_mg_media_item_Media_2`.`media_id` = `xf_mg_comment`.`content_id`)
LEFT JOIN `xf_mg_category` AS `xf_mg_category_Category_3` ON (`xf_mg_category_Category_3`.`category_id` = `xf_mg_album_Album_1`.`category_id`)
LEFT JOIN `xf_mg_album` AS `xf_mg_album_Album_4` ON (`xf_mg_album_Album_4`.`album_id` = `xf_mg_media_item_Media_2`.`album_id`)
LEFT JOIN `xf_mg_category` AS `xf_mg_category_Category_5` ON (`xf_mg_category_Category_5`.`category_id` = `xf_mg_media_item_Media_2`.`category_id`)
LEFT JOIN `xf_mg_rating` AS `xf_mg_rating_Rating_6` ON (`xf_mg_rating_Rating_6`.`rating_id` = `xf_mg_comment`.`rating_id`)
                        WHERE (`xf_mg_comment`.`comment_state` = 'visible')
                        ORDER BY `xf_mg_comment`.`comment_date` DESC, `xf_mg_comment`.`comment_id` DESC

LIMIT 50
If you don't want to include this, you should disable the widget creation on upgrade.
 

Mike

XenForo developer
Staff member
Would it be possible for you to show us the EXPLAIN output for that query on your install? (Add "EXPLAIN" at the beginning of it and run it.) I have some potential guesses as to what it's doing, but we may need to add a constraint/force index to get it to use the xf_mg_comment.comment_date index.

(The constraint is a reasonable idea anyway; it matches the behavior in other similar widgets.)
 

nocte

Well-known member
Hope this helps:

Code:
executing              | SELECT `xf_mg_comment`.*, `xf_mg_album_Album_1`.*, `xf_mg_media_item_Media_2`.*, `xf_mg_category_Category_3`.*, `xf_mg_album_Album_4`.*, `xf_mg_category_Category_5`.*, `xf_mg_rating_Rating_6`.*
                        FROM `xf_mg_comment`
                        LEFT JOIN `xf_mg_album` AS `xf_mg_album_Album_1` ON (`xf_mg_comment`.`content_type` = 'xfmg_album' AND `xf_mg_album_Album_1`.`album_id` = `xf_mg_comment`.`content_id`)
LEFT JOIN `xf_mg_media_item` AS `xf_mg_media_item_Media_2` ON (`xf_mg_comment`.`content_type` = 'xfmg_media' AND `xf_mg_media_item_Media_2`.`media_id` = `xf_mg_comment`.`content_id`)
LEFT JOIN `xf_mg_category` AS `xf_mg_category_Category_3` ON (`xf_mg_category_Category_3`.`category_id` = `xf_mg_album_Album_1`.`category_id`)
LEFT JOIN `xf_mg_album` AS `xf_mg_album_Album_4` ON (`xf_mg_album_Album_4`.`album_id` = `xf_mg_media_item_Media_2`.`album_id`)
LEFT JOIN `xf_mg_category` AS `xf_mg_category_Category_5` ON (`xf_mg_category_Category_5`.`category_id` = `xf_mg_media_item_Media_2`.`category_id`)
LEFT JOIN `xf_mg_rating` AS `xf_mg_rating_Rating_6` ON (`xf_mg_rating_Rating_6`.`rating_id` = `xf_mg_comment`.`rating_id`)
                        WHERE (`xf_mg_comment`.`comment_state` = 'visible')
                        ORDER BY `xf_mg_comment`.`comment_date` DESC, `xf_mg_comment`.`comment_id` DESC
LIMIT 50


+----+-------------+---------------------------+------------+--------+---------------+---------+---------+--------------------------------------------------+---------+----------+----------------------------------------------------+
| id | select_type | table                     | partitions | type   | possible_keys | key     | key_len | ref                                              | rows    | filtered | Extra                                              |
+----+-------------+---------------------------+------------+--------+---------------+---------+---------+--------------------------------------------------+---------+----------+----------------------------------------------------+
|  1 | SIMPLE      | xf_mg_comment             | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                             | 4898387 |    33.33 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | xf_mg_album_Album_1       | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xenforo_151202.xf_mg_comment.content_id          |       1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | xf_mg_media_item_Media_2  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xenforo_151202.xf_mg_comment.content_id          |       1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | xf_mg_category_Category_3 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                                             |       1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | xf_mg_album_Album_4       | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xenforo_151202.xf_mg_media_item_Media_2.album_id |       1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | xf_mg_category_Category_5 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                                             |       1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | xf_mg_rating_Rating_6     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xenforo_151202.xf_mg_comment.rating_id           |       1 |   100.00 | NULL                                               |
+----+-------------+---------------------------+------------+--------+---------------+---------+---------+--------------------------------------------------+---------+----------+----------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
 

XF Bug Bot

XenForo bug fixer bot
Staff member
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XFMG release (2.1.7).

Change log:
Improve performance of comments widget by constraining the query to only recent comments and set a hint toward the comment_date index.
There may be a delay before changes are rolled out to the XenForo Community.
 
Top