Extremely SLOW query...

[sunygxc]

Hi guys,

Two to three times per day a massive query comes through that takes almost 300 seconds to complete and in the process hangs all additional queries/posts in the process. All my top stats are normal and there is no excessive cpu usage (I'm running 9 webservers and one db server that's loaded to the nine's).

Here's the query that comes through that takes forever to complete...any idea what this is or what it's asking for or to reduce or eliminate the time time it takes to run?

SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
user.*, userfield.*, usertextfield.*,
icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.heig
ht AS avheight,
deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason,
postparsed.pagetext_html, postparsed.hasimages,
sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid


FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)
LEFT JOIN deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND deletionlog.type = 'post')
LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = 11 AND postparsed.languageid = 1)
LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 11 AND sigparsed.languageid = 1)
LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)

WHERE post.postid IN (0,30330141)
ORDER BY post.dateline DESC;

Any help would be really appreciated!

-Blair