Issue Bug Closed

#65697[ad Bug #65634] Backend performance improvement: SQL Indexes not used in printLogErrorMessages

Christian Plattner 03/12/15, 14:23 updated 03/01/17, 16:49

Description

Bug #65634 was not resolved correctly.
The merged patch adds “AND action<256” to the SQL query which does not optimize the query.
Actually, “AND action IN (-1,0,1,2)” or something more restrictive should have been added.

Currently, many rows must be scanned by MySQL:

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 |
+----+-------------+---------+------+-----------------+-------+---------+-------+-------+-------------+

With the IN() clause, only 4 rows must be read:

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 |
+----+-------------+---------+-------+-----------------+-----------+---------+------+------+-------------+

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.