Fixed Whats New / Media Comments is causing slow query

[Kirby]

Affected version

2.1.2

SELECT `xf_mg_media_item`.*, `xf_mg_album_Album_1`.*, `xf_mg_category_Category_2`.*, `xf_user_User_3`.*, `xf_attachment_Attachment_4`.*, `xf_attachment_data_Data_5`.*, `xf_permission_cache_content_Permissions_6`.*
FROM `xf_mg_media_item`
LEFT JOIN `xf_mg_album` AS `xf_mg_album_Album_1` ON (`xf_mg_album_Album_1`.`album_id` = `xf_mg_media_item`.`album_id`)
LEFT JOIN `xf_mg_category` AS `xf_mg_category_Category_2` ON (`xf_mg_category_Category_2`.`category_id` = `xf_mg_media_item`.`category_id`)
LEFT JOIN `xf_user` AS `xf_user_User_3` ON (`xf_user_User_3`.`user_id` = `xf_mg_media_item`.`user_id`)
LEFT JOIN `xf_attachment` AS `xf_attachment_Attachment_4` ON (`xf_attachment_Attachment_4`.`content_type` = 'xfmg_media' AND `xf_attachment_Attachment_4`.`content_id` = `xf_mg_media_item`.`media_id`)
LEFT JOIN `xf_attachment_data` AS `xf_attachment_data_Data_5` ON (`xf_attachment_data_Data_5`.`data_id` = `xf_attachment_Attachment_4`.`data_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_6` ON (`xf_permission_cache_content_Permissions_6`.`content_type` = 'xfmg_category' AND `xf_permission_cache_content_Permissions_6`.`content_id` = `xf_mg_category_Category_2`.`category_id` AND `xf_permission_cache_content_Permissions_6`.`permission_combination_id` = 'X')
WHERE (`xf_mg_media_item`.`last_comment_date` > 0) AND (`xf_mg_media_item`.`media_state` <> 'deleted') AND (`xf_mg_media_item`.`last_comment_date` > Y)
ORDER BY `xf_mg_media_item`.`last_comment_date` DESC, `xf_mg_media_item`.`media_id` DESC
LIMIT 200;

We do get a bunch of those queries logged every day.
As we do not even have comments (but over 400K images) this is kinda wasteful.

 

[Chris D]

We think this is fixable by adding a new index on last_comment_date on the xf_mg_media_item table.

Because it's a fairly large table, it's not actually something we'll be doing in 2.1 and we may save it for 2.2.

In the meantime, it would be ideal if you could try adding that index to see if it makes an improvement.

 

[Kirby]

Sorry, forgot to mention that I did add the index on last_comment_date after seeing this slow query
This does indeed fix the issue.

 

[Naz]

An index on last_comment_date was added in XFMG 2.2.