[TYPO3-dev] Optimizing last 20 query

[Martin Kutschker]

John Angel schrieb:
> Hi Dmitry,
> 
>> You can try the following composite keys:
> 
> Thanks, your keys are working fine for single page ID:
> 
> ALTER TABLE pages ADD INDEX `excerpts` ( 
> pid,nav_hide(1),deleted(1),hidden(1),crdate);
> ALTER TABLE tt_content ADD INDEX `excerpts` ( 
> pid,sys_language_uid,deleted(1),t3ver_state(1),hidden(1));
> 
> - pages table using key excerpts: ref: const,const,const,const, extra: 
> Using where
> - tt_content table using key excerpts: ref: cms.pages.uid,const,const, 
> extra: Using where
> 
> But when having several IDs in the SQL: pages.pid in IN ( 1000,1020,1040 
> ) instead of pages.pid in (1000), "Using filesort" suddenly appears:
> 
> - pages table using key excerpts: ref: NULL, extra: Using where; Using 
> filesort
> - tt_content table using key excerpts: ref: cms.pages.uid,const,const, 
> extra: Using where

Did you try my suggestion with moving the page select into to on-clause? 
Maybe the smaller set on the page rids you of the file sort.

Masi