Issue Bug Closed

#76940In table sys_file_reference a key for the field "uid_local" should be inserted

Klaus Hörmann-Engl 07/05/16, 13:01 updated 10/02/18, 11:09

Description

We came across this query when looking at slow log output of mariadb:

SELECT * FROM sys_file_reference WHERE  deleted = N AND hidden = N AND uid_local = N ORDER BY sorting_foreign;

When using the MySQL explain feature following showed up:

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)

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:

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)

This would result in a performance boost. What do you guys think?

Reviews 2