#65697[ad Bug #65634] Backend performance improvement: SQL Indexes not used in printLogErrorMessages
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.