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

[R. van Twisk]

Martin Kutschker wrote:
> R. van Twisk schrieb:
>   
>> 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.
>>     
>
> I might do it, but if it does it didn't tell me via the EXPLAIN.
>   
I need to read back upon this,

But I remember reading somewhere that EXPLAIN in mysql sometimes 'lies'.
This means that EXPLAIN tells us that it want's to use a index, while in 
reality mysql will not.

using EXPLAIN EXTENDED

You can find out what was realy used.

In short

1) EXPLAIN will give you a hint about what indexes might going to be used
2) EXPLAIN EXTENDED will give you what was actually used.

see : http://dev.mysql.com/doc/refman/5.0/en/explain.html
[ When the |EXTENDED| keyword is used, |EXPLAIN| produces extra 
information that can be viewed by issuing a |SHOW WARNINGS| statement 
following the |EXPLAIN| statement. This information displays how the 
optimizer qualifies table and column names in the |SELECT| statement, 
what the |SELECT| looks like after the application of rewriting and 
optimization rules, and possibly other notes about the optimization 
process.]

I didn't test it here....
But can anybody test if the two results are the same?

Ries
> But as we're really talking about a usually small table it shouldn't matter 
> too much.
>
> Masi
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>   



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