[TYPO3-dev] Optimizing last 20 query

[R. van Twisk]

John Angel wrote:
> 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
>
>
> Many thanks,
> John 
>
> _______________________________________________
>   

You are asking if it can be done faster,

if so... is it currently slow on your server??

You could change the pages.pid IN (1000) to = pages.pid = 1000
and you could put a appropriate index.

I am not sure what SQL_NO_CACHE does, but it seems like non ANSI SQL?

The above query will find all tt_content record directly below page 1000,
are you looking for a query that can also find all tt_content records on
sub-pages below page 1000 aswell??

Ries