[TYPO3-dev] Optimizing last 20 query

[R. van Twisk]

Dmitry Dulepov wrote:
> John Angel wrote:
>   
>> Hi Peter,
>>
>>     
>>> As queries for latest records are pretty common it might be helpful to
>>> index crdate and tstamp by default.
>>>       
>> EXPLAIN says:
>> - pages: using parent key, rows: 5000, extra info: Using where; Using
>> filesort
>>     
>
> Last one is bad. Filesort is the worst thing that you can get. It 
> definitely says that you are missing keys for sorting.
>
> You can try the following composite keys:
>
> pages.pid,pages.nav_hide(1),pages.deleted(1),pages.hidden(1),pages.cr_date
>
> tt_content.sys_language_uid,tt_content.deleted(1),tt_content.t3ver_state(1),tt_content.hidden(1)
>
> but exactly in this order (in the order of query).
>
> See what EXPLAIN says. If it does not use any of indexes, start removing 
> field by field from beginning (but do not remove sort field). If it does 
> not help, revert to the full index and add pages.uid to the first one 
> (at the beginning).
>
> This is a start, you may need to play with it to optimize.
>
>   
Hey Dimitry,

I never knew that the order is so important for mysql, if so
I feel that the query optimizer doesn't do a good job at all.

Where can I read about that the order is so important?
I never read that in the mysql docs...


Ries