Too many queries?

[Hugo Holbling]

Can anyone help me identify what this is doing? As far as I can tell (I don't know much about programming) it's causing 15 queries on showthread. :erm:

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.height AS avheight,
	spamlog.postid AS spamlog_postid,
	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, editlog.hashistory,
	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
	
	,blog_user.entries, gcache.ids as gcacheids, gcache.dateline as gcachedateline
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 spamlog AS spamlog ON(spamlog.postid = post.postid)
	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 = 1 AND postparsed.languageid = 1)
LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 1 AND sigparsed.languageid = 1)
LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
	LEFT JOIN blog_user AS blog_user ON (blog_user.bloguserid = post.userid) LEFT JOIN gal_cache as gcache ON (post.postid = gcache.postid)
WHERE post.postid IN (0,76300,76304,76305)
ORDER BY post.dateline

I have the Geek Auto-linker installed but I'm pretty confident something is wrong here...

If anyone is feeling helpful, here's another. This one appears to be running for every user who posts in a thread, so the three queries are being repeated for each user. Obviously this is a problem.

SELECT 
	userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
	IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
	
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) 

WHERE user.userid = xx

I've been going through mods trying to figure out which one is causing this (same for the first code) but with no luck thus far.

[Lynne]

A "JOIN" is not a separate query. Each code block you posted is one query.

[Hugo Holbling]

Thanks, Lynne. That makes sense now.

[ReCom]

Instead of running one query for each user with the queries are different from each other on the 'WHERE' part like:

... WHERE user.userid = '12'

... WHERE user.userid = '222'

... WHERE user.userid = '109'

you can combine them all into one query by using ... WHERE user.userid IN ('12', '222', '109')

I'm not sure where the queries came from but if they were introduced by a mod you've installed, it's better to contact the mod creator to fix the queries. seriously, mod coders should always think about optimization at all time when coding their mods.

[Hugo Holbling]

Yes, that's the issue: this query is being repeated for each user, hence the number going up depending on how many people respond to a thread. I need to figure out which mod is causing it.

[Mosh]

Originally Posted by Hugo Holbling

Yes, that's the issue: this query is being repeated for each user, hence the number going up depending on how many people respond to a thread. I need to figure out which mod is causing it.

Easiest way to do that is to disable all your hacks, then enable each one individually (i.e. run each hack in isolation) until you hit on the one that is causing the issue.