Bug #65697

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

Added by Christian Plattner about 4 years ago. Updated about 2 years ago.

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

0%

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

Duplicates TYPO3 Core - Task #79696: Better index on sys_log Closed 2017-02-08
Follows TYPO3 Core - Bug #65634: Backend performance improvement: SQL Indexes not used in printLogErrorMessages Closed 2015-03-10

History

#1 Updated by Stephan Großberndt about 4 years ago

See http://docs.typo3.org/typo3cms/CoreApiReference/ApiOverview/SystemLog/Index.html

On a normal 4.5 installation I have

rec-cnt action
126248  0
26647   1
47340   2
2272    3
2685    4
14      5
1       6
53      9

rec-cnt type
61731   1
5448    2
3653    3
89965   4
36401   5
129     254
7936    255
rec-cnt error
90502   0
82495   1
31821   2
422     3
23      102
SELECT COUNT(*) rec-cnt, type, action, error FROM sys_log GROUP BY type, action, error ORDER BY type, action, error 
rec-cnt type action error
11         1      0    1
13302      1      1    0
5          1      1    1
72         1      1    2
19         1      1  102
44097      1      2    0
6          1      2    1
4          1      2  102
1849       1      3    0
1          1      3    1
2339       1      4    0
14         1      4    1
14         1      5    1
5042       2      1    0
9          2      1    1
11         2      1    2
332        2      4    0
1          2      6    0
53         2      9    0
3653       3      1    0
12064      4      0    0
77764      4      0    1
137        4      0    2
4671       5      0    1
31601      5      0    2
129        5      1    0
129      254      1    0
4276     255      1    0
3238     255      2    0
422      255      3    3

#2 Updated by Thomas Hohn about 2 years ago

I would mean that this issue has been solved through #79696

#3 Updated by Stephan Großberndt about 2 years ago

  • Status changed from New to Closed

Also available in: Atom PDF