vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Too many queries? (https://vborg.vbsupport.ru/showthread.php?t=197649)

Hugo Holbling 11-30-2008 09:06 AM
Too many queries?
 
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:

Code:
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.

Code:
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 11-30-2008 03:43 PM
A "JOIN" is not a separate query. Each code block you posted is one query.

Hugo Holbling 11-30-2008 05:09 PM
Thanks, Lynne. That makes sense now.

ReCom 12-01-2008 01:33 AM
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 12-01-2008 04:55 AM
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 12-01-2008 05:15 AM
Quote:
Originally Posted by Hugo Holbling (Post 1676222)
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.


All times are GMT. The time now is 02:33 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01045 seconds
  • Memory Usage 1,732KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (2)bbcode_code_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (6)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete