Bug #22986
closed
Slow query in sys_history due to missing key on tstamp / tablename
0%
Description
- Time: 100624 8:35:03
- User@Host: foo[bar] @ localhost []
- Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
DELETE FROM sys_history WHERE tstamp<1274769300 AND tablename='tt_news';
Create new key on tstamp + tablename, or change existing key combination / code
Initiated by:
t3lib/class.t3lib_tcemain.php
function clearHistory($maxAgeSeconds=604800,$table) {
(issue imported from #M14852)
Updated by Ernesto Baschny over 13 years ago
In my case, this key is used on that DELETE:
KEY `recordident` (`tablename`,`recuid`,`tstamp`),
mysql> explain select * FROM sys_history WHERE tstamp<1274769300 AND tablename='tt_news';---------------+-------------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------+-------------+------+---------------+-------------+---------+-------+------+-------------+
| 1 | SIMPLE | sys_history | ref | recordident | recordident | 767 | const | 1 | Using where |---------------+-------------+------+---------------+-------------+---------+-------+------+-------------+
So maybe that delay was caused by something else?
Updated by Christian Kuhn over 13 years ago
Resolved, no change required.
Ok, at least the first part of the key is used for me (tablename), maybe my table just wasn't big enough during my first tests.
In a perfect world we might think about switching the order of recuid and tstamp in the key for this query, but I'd say that we do this only if we have a real benefit. Furthermore this table usually doesn't grow that big to justify the needed work.
Updated by