AlexanderT
06-12-2007, 08:46 AM
Can you guys help me optimize this SQL query? It basically pulls out the latest ten threads with the condition that they are meant for the frontpage (custom column 'thread.frontpage' set to 1).
SELECT SQL_CACHE
thread.threadid, thread.title, thread.dateline, thread.postusername, thread.postuserid, thread.replycount, thread.pollid, thread.uploadid,
post.pagetext, post.attach, post.postid,
forum.forumid, forum.parentid, forum.title AS forumtitle,
editcp_uploads.dateline AS filedate, editcp_uploads.filename, editcp_uploads.filewidth AS imagewidth, editcp_uploads.fileheight AS imageheight
FROM thread AS thread
LEFT JOIN post ON(thread.firstpostid = post.postid)
LEFT JOIN forum ON(forum.forumid = thread.forumid)
LEFT JOIN editcp_uploads ON(thread.uploadid = editcp_uploads.id)
WHERE thread.frontpage = '1'
AND thread.forumid NOT IN (0,29)
AND thread.visible = 1
AND post.visible = 1
AND thread.open <> 10
ORDER BY thread.dateline DESC
LIMIT 0,10
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE thread range forumid forumid 2 10357 Using where; Using filesort
1 SIMPLE forum eq_ref PRIMARY PRIMARY 2 thread.forumid 1
1 SIMPLE editcp_uploads eq_ref PRIMARY PRIMARY 2 thread.uploadid 1
1 SIMPLE post eq_ref PRIMARY PRIMARY 4 thread.firstpostid 1 Using where
Current indices:
Keyname Type Cardinality Field
PRIMARY PRIMARY 10382 threadid
postuserid INDEX 1730 postuserid
pollid INDEX 188 pollid
dateline INDEX 10382 dateline
lastpost INDEX 10382 lastpost
lastposter INDEX 1483 lastposter
forumid INDEX 10382 forumid, visible, sticky, lastpost
title FULLTEXT 1 title
Basically I would prevent the query from going through all existing thread rows. If it's not possible to optimize this query, perhaps two more simple queries could do the trick?
Thanks!
SELECT SQL_CACHE
thread.threadid, thread.title, thread.dateline, thread.postusername, thread.postuserid, thread.replycount, thread.pollid, thread.uploadid,
post.pagetext, post.attach, post.postid,
forum.forumid, forum.parentid, forum.title AS forumtitle,
editcp_uploads.dateline AS filedate, editcp_uploads.filename, editcp_uploads.filewidth AS imagewidth, editcp_uploads.fileheight AS imageheight
FROM thread AS thread
LEFT JOIN post ON(thread.firstpostid = post.postid)
LEFT JOIN forum ON(forum.forumid = thread.forumid)
LEFT JOIN editcp_uploads ON(thread.uploadid = editcp_uploads.id)
WHERE thread.frontpage = '1'
AND thread.forumid NOT IN (0,29)
AND thread.visible = 1
AND post.visible = 1
AND thread.open <> 10
ORDER BY thread.dateline DESC
LIMIT 0,10
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE thread range forumid forumid 2 10357 Using where; Using filesort
1 SIMPLE forum eq_ref PRIMARY PRIMARY 2 thread.forumid 1
1 SIMPLE editcp_uploads eq_ref PRIMARY PRIMARY 2 thread.uploadid 1
1 SIMPLE post eq_ref PRIMARY PRIMARY 4 thread.firstpostid 1 Using where
Current indices:
Keyname Type Cardinality Field
PRIMARY PRIMARY 10382 threadid
postuserid INDEX 1730 postuserid
pollid INDEX 188 pollid
dateline INDEX 10382 dateline
lastpost INDEX 10382 lastpost
lastposter INDEX 1483 lastposter
forumid INDEX 10382 forumid, visible, sticky, lastpost
title FULLTEXT 1 title
Basically I would prevent the query from going through all existing thread rows. If it's not possible to optimize this query, perhaps two more simple queries could do the trick?
Thanks!