Bug #65634

Backend performance improvement: SQL Indexes not used in printLogErrorMessages

Added by Christian Plattner about 4 years ago. Updated 8 months ago.

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

100%

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

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


Related issues

Precedes TYPO3 Core - Bug #65697: [ad Bug #65634] Backend performance improvement: SQL Indexes not used in printLogErrorMessages Closed 2015-03-12

Associated revisions

Revision bd64f55b (diff)
Added by Andreas Fernandez about 4 years ago

[BUGFIX] Use "user_auth" index for DataHandler:printLogErrorMessages

Add the database field "action" to the SQL query to be able to use
the "user_auth" index and speed up the query.

Resolves: #65634
Releases: master, 6.2
Change-Id: I3371367a2fb7c86ea4cf38ee8f7ce1e8a571ffb8
Reviewed-on: http://review.typo3.org/37692
Reviewed-by: Nicole Cordes <>
Tested-by: Nicole Cordes <>
Reviewed-by: Jigal van Hemert <>
Tested-by: Jigal van Hemert <>

Revision 4f9799b1 (diff)
Added by Andreas Fernandez about 4 years ago

[BUGFIX] Use "user_auth" index for DataHandler:printLogErrorMessages

Add the database field "action" to the SQL query to be able to use
the "user_auth" index and speed up the query.

Resolves: #65634
Releases: master, 6.2
Change-Id: I3371367a2fb7c86ea4cf38ee8f7ce1e8a571ffb8
Reviewed-on: http://review.typo3.org/37698
Reviewed-by: Markus Klein <>
Tested-by: Markus Klein <>

History

#1 Updated by Gerrit Code Review about 4 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/37692

#2 Updated by Gerrit Code Review about 4 years ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/37692

#3 Updated by Gerrit Code Review about 4 years ago

Patch set 1 for branch TYPO3_6-2 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/37698

#4 Updated by Andreas Fernandez about 4 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#5 Updated by Christian Plattner about 4 years ago

May I add that your modification "AND action<256" did not help improving the performance:

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

Still, many rows have to be read. If you had added "AND action in (-1,0,1,2)", only 4 rows had to be read.

Follow-up bug report #65697 was created.

#6 Updated by Benni Mack 8 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF