Feature #24307
closed
sys_history: optimize indexes
0%
Description
The combined database key "recordident (tablename,recuid,tstamp)" for the table "sys_history" at the the file "typo3_src-4.5.0beta2/t3lib/stddb/tables.sql" works like the three single keys:
recordident_1 (tablename)
recordident_2 (tablename,recuid)
recordident_3 (tablename,recuid,tstamp)
But there is no sql statement using all three columns and the key generation is wasting resources. It should be changed to "recordident (tablename,recuid)". A second reason for the change is the high cardinality for the combination of tablename and recuid, so the third column has no positive effect.
Add a new key "tstampident (tablename,tstamp)" for clearing the history at t3lib_TCEmain::clearHistory() because the delete statement (WHERE tstamp<... AND tablename=...) uses only the first column "tablename" from the key "recordident" with a very poor cardinality at a large history table.
On our system the slow query reported at http://bugs.typo3.org/view.php?id=14852 is gone.
(issue imported from #M16698)
Files
Updated by Ernesto Baschny over 13 years ago
Sounds logical. So you propose is to:
alter table sys_history drop KEY `recordident`;
alter table sys_history add KEY `recordident_1` (`tablename`,`recuid`);
alter table sys_history add KEY `recordident_2` (`tablename`,`tstamp`);
Right? Christian Kuhn, what do you think? See attached patch (diff).
Updated by Steffen Kamper over 13 years ago
sounds good, but please rename the indizes, simply use
tablename_recuid (tablename, recuid); tablename_tstamp (tablename, tstamp);
Updated by Christian Kuhn over 13 years ago
mysql (afaik) background: mysql can use keys over multiple fields if they are given in where in a row:
In our current example:
KEY recordident (tablename,recuid,tstamp)> where tablename = foo and recuid = 42 and tstamp = 123 -> mysql uses full key.> where tablename = foo and recuid = 42 > mysql uses key on tablename and recuid> where tablename = foo and tstamp = 123 -> mysql uses key on tablename, but not on tstamp (it can't, because there is recuid in between).
--> if there are only queries like> where tablename = foo and recuid = 42> where tablename = foo and tstamp = 123
it would be better to key those two independ from each other like Juergen suggested. This seems to be the case in all current core queries.
Attached patch also changes the storage engine to InnoDB, which won't harm at this point and improves locking for this table.
For the cardinality: recuid and tstamp both have pretty much the same cardinality for me.
Updated by 
Updated by