Issue Bug Closed

#65634Backend performance improvement: SQL Indexes not used in printLogErrorMessages

Christian Plattner 03/10/15, 09:37 updated 10/02/18, 11:45

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

Reviews 2