Fixed Attachment manager in admin panel is extremely slow

[Jake B.]

Affected version

2.1.12

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

Run Time: 3.066389

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

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

 

[rdn]

There are ~3.6m attachments

Is this XDA?
Maybe the server has the issues rather than bug with XF?

 

[rdn]

1.4m attachment XF 2.2 forum debug stats.
37m post.

 

[duderuud]

On my forum (30mln posts) it's running fast too.

 

[Xon]

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.

 

[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 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.