Issue Feature Closed

#24307sys_history: optimize indexes

Juergen Deisenroth 12/07/10, 12:18 updated 03/29/11, 23:46

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.