Bug #65634
closed
Backend performance improvement: SQL Indexes not used in printLogErrorMessages
100%
Description
When profiling a backend save request, a huge amount of time can be seen to be lost in DataHandler::printLogErrorMessages. It issues a SELECT statement which needs a full scan of the sys_log table on each save request!
explain SELECT * FROM sys_log WHERE type=1 AND userid=1 AND tstamp=1425974587 AND error<>0; +----+-------------+---------+------+-----------------+-------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+-------+---------+-------+-------+-------------+ | 1 | SIMPLE | sys_log | ref | event,user_auth | event | 4 | const | 33398 | Using where | +----+-------------+---------+------+-----------------+-------+---------+-------+-------+-------------+
However, if the additional field `action` would be added to the WHERE clause, the key "user_auth" could be used to speed up the query:
explain SELECT * FROM sys_log WHERE type=1 AND userid=1 AND tstamp=1425974586 AND error<>0 and action in (-1,0,1,2,3); +----+-------------+---------+-------+-----------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+-----------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | sys_log | range | event,user_auth | user_auth | 6 | NULL | 4 | Using where | +----+-------------+---------+-------+-----------------+-----------+---------+------+------+-------------+
So I would suggest adding the WHERE statement " and action in (-1,0,1,2,3)" for a performance boost
Updated by Gerrit Code Review over 9 years ago
- Status changed from New to Under Review
Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/37692
Updated by Gerrit Code Review over 9 years ago
Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/37692
Updated by Gerrit Code Review over 9 years ago
Patch set 1 for branch TYPO3_6-2 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/37698
Updated by Andreas Fernandez over 9 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset bd64f55b29d21106f8abd79750df2bc1e73c595f.
Updated by Christian Plattner over 9 years ago
May I add that your modification "AND action<256" did not help improving the performance:
explain SELECT * FROM sys_log WHERE type=1 AND action<256 AND userid=1 AND tstamp=1425974587 AND error<>0; +----+-------------+---------+------+-----------------+-------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+-------+---------+-------+-------+-------------+ | 1 | SIMPLE | sys_log | ref | event,user_auth | event | 4 | const | 33532 | Using where | +----+-------------+---------+------+-----------------+-------+---------+-------+-------+-------------+
Still, many rows have to be read. If you had added "AND action in (-1,0,1,2)", only 4 rows had to be read.
Follow-up bug report #65697 was created.
Updated by