[TYPO3-dev] Optimizing last 20 query

[Peter Russ]

John Angel schrieb:
> Hi!
> 
> Can this be done faster?
> 
> Here is the SQL query providing last 20 pages under page id "1000":
> 
> 
> SELECT SQL_NO_CACHE pages.crdate, pages.title, tt_content.bodytext
> FROM tt_content
> INNER JOIN pages ON tt_content.pid = pages.uid
> WHERE pages.nav_hide =0
> AND pages.pid
> IN ( 1000 )
> AND tt_content.sys_language_uid =0
> AND tt_content.deleted =0
> AND tt_content.t3ver_state !=1
> AND tt_content.hidden =0
> AND pages.deleted =0
> AND pages.t3ver_state !=1
> AND pages.hidden =0
> ORDER BY pages.crdate DESC
> LIMIT 0 , 20

To improve performance of the query you should add indexes:
to pages.t3ver_state, tt_content.t3ver_state and pages.crdate
If there are only 2 values for t3ver_state, e.g. 0 or 1 index for 
pages.crdate only would be ok. Just check it out.

As queries for latest records are pretty common it might be helpful to 
index crdate and tstamp by default.


Regs. Peter.


-- 
Fiat lux!
Docendo discimus.
_____________________________
4Many® Services
openBC: http://www.openbc.com/go/invuid/Peter_Russ