TYPO3 Core

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).

I'll take a look, soon.

sounds good, but please rename the indizes, simply use

tablename_recuid  (tablename, recuid);
tablename_tstamp  (tablename, tstamp);

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.

Sent to core list.

Committed to trunk rev 10068