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

[nocte]

Affected version

2.1.6

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:

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]

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]

Hope this helps:

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]

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.