[TASK] Better index on sys_log 95/51595/6
authorThomas Hohn <thomas@hohn.dk>
Wed, 8 Feb 2017 19:32:31 +0000 (20:32 +0100)
committerGeorg Ringer <georg.ringer@gmail.com>
Sun, 12 Feb 2017 18:27:33 +0000 (19:27 +0100)
Looking at the selects performed on sys_log in the core
of TYPO3 reveals that a combination of tstamp, type, userid
should giver a better performance for at least 2 SQL
statements. One performing a full-table scan - the other
approximately half a full-table scan.

On installations where a few users have created a lot of entries
in sys_log the existing indexes event and user_auth will return
too many rows compared to the new combined index.

Especially the method printLogErrorMessages in the DataHandler
benefits from this index.

Resolves: #79696
Releases: master

Change-Id: I3e115b8b5a3cc9cd134d0cc995a06488134be40d
Reviewed-on: https://review.typo3.org/51595
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
Reviewed-by: Georg Ringer <georg.ringer@gmail.com>
Tested-by: Georg Ringer <georg.ringer@gmail.com>
typo3/sysext/core/ext_tables.sql

index 666c751..60f77c3 100644 (file)
@@ -619,7 +619,8 @@ CREATE TABLE sys_log (
        KEY event (userid,event_pid),
        KEY recuidIdx (recuid,uid),
        KEY user_auth (type,action,tstamp),
-       KEY request (request_id)
+       KEY request (request_id),
+       KEY combined_1 (tstamp, type, userid)
 ) ENGINE=InnoDB;
 
 #