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

[R. van Twisk]

Dmitry Dulepov wrote:
> Hi!
>
> Martin Kutschker wrote:
>   
>> 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.
>>     
>
> Yes but if all fields are in the index, it means that mysql need not to 
> look to actual rows to find the right row. Looking in the index is much 
> faster.
>
>   
>> My conclusio: In theory your index should be faster, but in practice it 
>> isn't.
>>     
>
> I think it is possible to evaluate only by runing dedicated test.
>
>   
Since the number of columns is small,
mysql might even decide not to use a index at all, and simply load the 
complete table in memory.
 From there it's faster to load/sort and select the data because all 
data is in memory already.
This will save HD overhead (extra seeks). I know at least postgreSQL can 
do it like that
when the optimizer decides (he will... I did test that).

Indexes only work really well on large tables with many records.
My +3000 pages website has about 34 records on sys_template where around
18 are active (means deleted = 0). The total size of the template is 
46Kb, 7Kb is index.

So if Mysql is smart (sometimes it really isn't smart) then it would 
load the complete table
in memory and basically eliminating the index at all. This will save 
HD's seeks.

I just hope Mysql is smart :)

Ries



-- 
Ries van Twisk
Freelance Typo3 Developer
=== Private:
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/freelance-typo3.html
skype: callto://r.vantwisk
=== Work:
email: ries at livetravelguides.com
web:   http://www.livetravelguides.com