Project

General

Profile

Actions

Bug #65697

closed

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

Added by Christian Plattner over 9 years ago. Updated over 7 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
DataHandler aka TCEmain
Target version:
-
Start date:
2015-03-12
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

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


Related issues 2 (0 open2 closed)

Is duplicate of TYPO3 Core - Task #79696: Better index on sys_log ClosedThomas Hohn2017-02-08

Actions
Follows TYPO3 Core - Bug #65634: Backend performance improvement: SQL Indexes not used in printLogErrorMessagesClosed2015-03-10

Actions
Actions

Also available in: Atom PDF