Project

General

Profile

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?

Back