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

[Marco van Herwaarden]

Your support ticket regarding this is already flagged for developer review. Once a developer has the time, they will look into your issue.

[Eikinskjaldi]

In the meantime, the query looks pretty optimised, even with a filesort.

login to mysql and run show full processlist to see exactly what operation is hanging.

[Marco van Herwaarden]

The issue was investigated in a support ticket and the problem doesn't seem to be caused by this query. The problem is most likely caused by something else running on the server locking up either server resources or the MySQL tables, resulting in a very long execution time for this query.

[Eikinskjaldi]

show full processlist will still help, assuming it is a query that is locking the tables.