[TYPO3-dev] Optimizing last 20 query

[Dmitry Dulepov]

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.

-- 
Dmitry Dulepov

Web: http://typo3bloke.net/
Skype: callto:liels_bugs

"It is our choices, that show what we truly are,
far more than our abilities." (A.P.W.B.D.)