Fixed Attachment manager in admin panel is extremely slow

Affected version
2.1.12

Jake B.

Well-known member
Each page load takes around 5 seconds, most of which is spent on the following queries:

Run Time: 3.066389
SQL:
SELECT COUNT(*)
FROM `xf_attachment`
INNER JOIN `xf_attachment_data` AS `xf_attachment_data_Data_1` ON (`xf_attachment_data_Data_1`.`data_id` = `xf_attachment`.`data_id`)

Run time: 1.952527
SQL:
SELECT `xf_attachment`.*, `xf_attachment_data_Data_1`.*
FROM `xf_attachment`
INNER JOIN `xf_attachment_data` AS `xf_attachment_data_Data_1` ON (`xf_attachment_data_Data_1`.`data_id` = `xf_attachment`.`data_id`)
ORDER BY `xf_attachment`.`attach_date` DESC
LIMIT 20 OFFSET 20

There are ~3.6m attachments
 

Xon

Well-known member
There doesn't look to be an index that MySQL 8 will pick when ordering by attach_date (or file_size) in the admincp. This results in a full-table scan of the xf_attachment + xf_attachment_data tables.
 
Last edited:

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 XF release (2.2.7).

Change log:
Improve performance of the attachment manager when working with mind-boggling numbers of attachments
There may be a delay before changes are rolled out to the XenForo Community.
 
Top