- Affected version
- 2.1.7
SQL:
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
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 |
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 |