Bug #76940
Updated by Markus Klein over 7 years ago
We came across this query when looking at slow log output of mariadb: <pre> SELECT * FROM sys_file_reference WHERE deleted = N AND hidden = N AND uid_local = N ORDER BY sorting_foreign; </pre> When using the MySQL explain feature following showed up: <pre> MariaDB [t3database]> explain SELECT * FROM sys_file_reference WHERE deleted = 0 AND hidden = 0 AND uid_local = 123 ORDER BY sorting_foreign; +------+-------------+--------------------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | sys_file_reference | ALL | deleted | NULL | NULL | NULL | 5762 | Using where; Using filesort | +------+-------------+--------------------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) </pre> It basically says that 5762 rows need to bee looked at to find the correct one. After adding a key to the table for the field "uid_local" the same query showed up as: <pre> MariaDB [t3database]> explain SELECT * FROM sys_file_reference WHERE deleted = 0 AND hidden = 0 AND uid_local = 123 ORDER BY sorting_foreign; +------+-------------+--------------------+------+-------------------+-----------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------------+------+-------------------+-----------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | sys_file_reference | ref | deleted,uid_local | uid_local | 4 | const | 1 | Using where; Using filesort | +------+-------------+--------------------+------+-------------------+-----------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) </pre> This would result in a performance boost. What do you guys think?