f0urtyfive
03-17-2005, 04:07 PM
I'm getting this query thats caused by vbadvanced, that I believe is getting the news topics... I was wondering if anyone had any insight in speeding it up. right now execution time is about 1s to 1.5s.
SELECT
IF(votenum >= 2, votenum, 0) AS votenum, IF(votenum >= 2 AND votenum != 0, votetotal / votenum, 0) AS voteavg,
thread.threadid, thread.title, replycount, postusername, postuserid, thread.dateline AS postdateline, IF(views<=replycount, replycount+1, views) AS views, forumid, post.postid, pagetext, allowsmilie, showsignature
,thread.iconid AS threadiconid
, attachment.filename, attachment.filesize, attachment.visible, attachmentid, counter, thumbnail, LENGTH(thumbnail) AS thumbnailsize
FROM thread AS thread
LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
LEFT JOIN user AS user ON (user.userid = post.userid)
LEFT JOIN attachment AS attachment ON (post.postid = attachment.postid)
WHERE forumid IN(113) AND thread.visible = '1'
GROUP BY post.postid
ORDER BY postdateline DESC, attachmentid LIMIT 30;
Here's the explain of it:
+------------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+
| thread | ref | forumid | forumid | 4 | const,const | 15252 | Using where; Using temporary; Using filesort |
| post | eq_ref | PRIMARY | PRIMARY | 4 | thread.firstpostid | 1 | |
| user | eq_ref | PRIMARY | PRIMARY | 4 | post.userid | 1 | Using index |
| attachment | ref | postid | postid | 4 | post.postid | 1 | |
+------------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+
SELECT
IF(votenum >= 2, votenum, 0) AS votenum, IF(votenum >= 2 AND votenum != 0, votetotal / votenum, 0) AS voteavg,
thread.threadid, thread.title, replycount, postusername, postuserid, thread.dateline AS postdateline, IF(views<=replycount, replycount+1, views) AS views, forumid, post.postid, pagetext, allowsmilie, showsignature
,thread.iconid AS threadiconid
, attachment.filename, attachment.filesize, attachment.visible, attachmentid, counter, thumbnail, LENGTH(thumbnail) AS thumbnailsize
FROM thread AS thread
LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
LEFT JOIN user AS user ON (user.userid = post.userid)
LEFT JOIN attachment AS attachment ON (post.postid = attachment.postid)
WHERE forumid IN(113) AND thread.visible = '1'
GROUP BY post.postid
ORDER BY postdateline DESC, attachmentid LIMIT 30;
Here's the explain of it:
+------------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+
| thread | ref | forumid | forumid | 4 | const,const | 15252 | Using where; Using temporary; Using filesort |
| post | eq_ref | PRIMARY | PRIMARY | 4 | thread.firstpostid | 1 | |
| user | eq_ref | PRIMARY | PRIMARY | 4 | post.userid | 1 | Using index |
| attachment | ref | postid | postid | 4 | post.postid | 1 | |
+------------+--------+---------------+---------+---------+--------------------+-------+----------------------------------------------+