Actions
Bug #76940
closedIn table sys_file_reference a key for the field "uid_local" should be inserted
Status:
Closed
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2016-07-05
Due date:
% Done:
100%
Estimated time:
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
no-brainer
Is Regression:
No
Sprint Focus:
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?
Actions