[TYPO3-dev] Optimizing last 20 query

[John Angel]

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

And yes, the order of indexes is very important. Don't know why.

Regards,
John