Widget slowly query in gallery

[kick]

Affected version

2.1.7

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`.*
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`)
WHERE (IF(`xf_mg_media_item`.`album_id` > 0, `xf_mg_album_Album_1`.`album_state` = 'visible', 1=1)) AND ((`xf_mg_media_item`.`category_id` IN (1, 2)) OR (`xf_mg_media_item`.`category_id` = 0)) AND (`xf_mg_media_item`.`media_state` = 'visible')
ORDER BY `xf_mg_media_item`.`media_date` DESC, `xf_mg_media_item`.`media_id` DESC

Run Time: 0.001498

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_mg_media_item	ALL	category_id_media_date				2475	Using where; Using temporary; Using filesort
SIMPLE	xf_mg_album_Album_1	eq_ref	PRIMARY	PRIMARY	4	xf_mg_media_item.album_id	1	Using where
SIMPLE	xf_mg_category_Category_2	ALL	PRIMARY				2	Using where; Using join buffer (Block Nested Loop)
SIMPLE	xf_user_User_3	eq_ref	PRIMARY	PRIMARY	4	xf_mg_media_item.user_id	1
SIMPLE	xf_attachment_Attachment_4	ref	content_type_id_date	content_type_id_date	31	const,xf_mg_media_item.media_id	1	Using where
SIMPLE	xf_attachment_data_Data_5	eq_ref	PRIMARY	PRIMARY	4	xf_attachment_Attachment_4.data_id	1

explain:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xf_mg_media_item	NULL	ALL	category_id_media_date	NULL	NULL	NULL	2475	33.09	Using where; Using temporary; Using filesort
1	SIMPLE	xf_mg_album_Album_1	NULL	eq_ref	PRIMARY	PRIMARY	4	xf_mg_media_item.album_id	1	100.00	Using where
1	SIMPLE	xf_mg_category_Category_2	NULL	ALL	PRIMARY	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	xf_user_User_3	NULL	eq_ref	PRIMARY	PRIMARY	4	xf_mg_media_item.user_id	1	100.00	NULL
1	SIMPLE	xf_attachment_Attachment_4	NULL	ref	content_type_id_date	content_type_id_date	31	const,xf_mg_media_item.media_id	1	100.00	Using where
1	SIMPLE	xf_attachment_data_Data_5	NULL	eq_ref	PRIMARY	PRIMARY	4	xf_attachment_Attachment_4.data_id	1	100.00	NULL