[TYPO3-dev] rfc: minor db change for TS templates

[Martin Kutschker]

Dmitry Dulepov schrieb:
> Hi!
> 
> Martin Kutschker wrote:
> 
>> SELECT * FROM sys_template WHERE pid=1 AND deleted=0 AND hidden=0 AND 
>> (starttime<=1157534861) AND (endtime=0 OR endtime>1157534861) ORDER BY 
>> sorting LIMIT 1;
> 
> 
> ...
> 
>> Not much, but a filesort is avoided.
> 
> 
> Anyway, it is better than current. Since you already started it, what 
> happens if you put index like this:
> 
>     KEY parent3 (pid,deleted,hidden,starttime,endtime,sorting)
> 
> Not a small key and I am not sure that MySQL will really consider it 
> (there is a limit for key after which mysql does not use it). But i it 
> does, it make perform all query using this index.

The EXPLAIN returns the same. But of course you're right Mysql should not 
read the row to handle the WHERE. But it seems to work on the index alone 
you have to use only indexed columns in the SELECT. Here we use *, so 
AFAIKS Mysql does no further optimization.

My conclusio: In theory your index should be faster, but in practice it isn't.

Masi