[TYPO3-dev] Optimizing last 20 query

[Staffan Ericsson]

John Angel skrev:
> Hi!
> 
> Can this be done faster?

SELECT pages.crdate, pages.title, tt_content.bodytext
FROM (SELECT pages.crdate, page.title, pages.uid
	FROM pages
	WHERE pages.pid IN (1000)
	AND pages.deleted = 0
	AND pages.t3ver_state !=1
	AND pages.hidden = 0
	AND pages.nav_hide = 0) AS cur_pages,
     (SLECT tt_content.bodytext, content.pid
	FROM tt_content
	WHERE tt_content.sys_language_uid = 0
	AND tt_content.deleted = 0
	AND tt_content.t3_ver_state !=1
	AND tt_content.hidden = 0) AS cur_content
WHERE cur_pages.uid = cur_content.pid
ORDER BY pages.crdate, DESC
LIMIT 0, 20


Might be more effecient as it filters data before the join, but at the
same time the query optimisier should do the same work.

.staffan


> 
> 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