#24307sys_history: optimize indexes
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)
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.