As a resolution for the issue #2610126: admin/content/files is slow with many files → a new index is required. Upon creating the index the field filename is varchar 255 the field in the index is getting truncated to 191 chars. Due to this mysql is not using the index.
1. After creating index through drupal, perform explain using
explain SELECT file_managed.filename AS file_managed_filename, file_managed.created AS file_managed_created, file_managed.changed AS file_managed_changed, file_managed.fid AS fid, SUM(file_usage_file_managed.count) AS file_usage_file_managed_count, MIN(file_managed.fid) AS fid_1 FROM file_managed file_managed LEFT JOIN file_usage file_usage_file_managed ON file_managed.fid = file_usage_file_managed.fid GROUP BY file_managed.fid, file_managed_filename, file_managed.filemime,file_managed.filesize,file_managed.status, file_managed_created, file_managed_changed ORDER BY file_managed_created DESC LIMIT 51 OFFSET 0;
the result is that the index is not used
+------+-------------+-------------------------+------+------------------------------+---------+---------+---------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------------------+------+------------------------------+---------+---------+---------------------+--------+---------------------------------+
| 1 | SIMPLE | file_managed | ALL | NULL | NULL | NULL | NULL | 259671 | Using temporary; Using filesort |
| 1 | SIMPLE | file_usage_file_managed | ref | PRIMARY,fid_count,fid_module | PRIMARY | 4 | db.file_managed.fid | 1 | |
+------+-------------+-------------------------+------+------------------------------+---------+---------+---------------------+--------+---------------------------------+
2. Create the same index manually at the prompt using alter table file_managed add index groupidx(fid, filename, filemime,filesize,status, created, changed); and perform explain
+------+-------------+-------------------------+-------+------------------------------+---------------------------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------------------------+-------+------------------------------+---------------------------+---------+---------------------+------+----------------------------------------------+
| 1 | SIMPLE | file_managed | index | NULL | file__admin_content_files | 1304 | NULL | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | file_usage_file_managed | ref | PRIMARY,fid_count,fid_module | PRIMARY | 4 | db.file_managed.fid | 1 | |
+------+-------------+-------------------------+-------+------------------------------+---------------------------+---------+---------------------+------+----------------------------------------------+
index gets used.
Most varchar fields are 255 in length, increase the check from > 191 to > 255