#65634Backend performance improvement: SQL Indexes not used in printLogErrorMessages
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