The Arcive of Official vBulletin Modifications Site.

X-Fan · #21 11-04-2002, 05:06 AM

Yep, all evaluated templates are in the $templatesused section, and the page is still generating around 50 queries. I'm stumped.

Tigga · #22 11-04-2002, 07:04 AM

That is odd... Why don't you copy and past the results of an explain=1 here. It would help to see the queries.
I just looked at your forum again. There's not really any hacks I can see that would cause all the count queries except maybe the part where it shows the % of posts a user has made. I'm really not sure about that since I haven't used that hack, but it might be worth looking at.

Erwin · #23 11-04-2002, 08:51 AM

Yup, place "debug=1;" on a new line in your admin/config.php then place ?explain=1 at the end of a showthread URL, and post the contents here.

X-Fan · #24 11-10-2002, 01:20 AM

Okay, here we go... from the following thread:

http://x-mencomics.com/xfan/forums/s...&showqueries=1

PHP Code:


			
Query: SELECT template FROM template WHERE title='options'

Time before: 0.033180952072144

Time after:  0.035552024841309



--------------------------------------------------------------------------------



Query: SELECT sessionhash,userid,host,useragent,styleid FROM session WHERE lastactivity>1036951798 AND sessionhash='85bbc2f6adbf444240a47dc0e7aea108' AND (host='203.173.250.142' OR (althash='' AND althash<>'')) AND useragent='Mozilla/4.0 (compatible; MSIE 6.0; Windows 98; Win'

Time before: 0.043159008026123

Time after:  0.043928027153015



--------------------------------------------------------------------------------



Query: SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.userid='1'

Time before: 0.044394016265869

Time after:  0.045485019683838



--------------------------------------------------------------------------------



Query: SELECT * FROM thread WHERE threadid=12267

Time before: 0.047382950782776

Time after:  0.04817795753479



--------------------------------------------------------------------------------



Query: SELECT forum.forumid,styleid,styleoverride FROM forum,thread WHERE forum.forumid=thread.forumid AND threadid='12267'

Time before: 0.048655033111572

Time after:  0.04926598072052



--------------------------------------------------------------------------------



Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='1' or styleid=1 ORDER BY styleid DESC

Time before: 0.049590945243835

Time after:  0.049991011619568



--------------------------------------------------------------------------------



Query: SELECT template,title

                          FROM template

                          WHERE (title IN ('showthread_ratingdisplay','postbit_search','postbit_buddy','postbit_useremail','icq','aim','yahoo','postbit_homepage','postbit_profile','postbit_ip_show','postbit_ip_hidden','postbit','postbit_sendpm','postbit_avatar','postbit_offline','postbit_online','postbit_editedby','postbit_signature','postbit_attachment','postbit_attachmentimage','showthread_adminoptions','showthread_threadrate','showthread_pollresults_voted','showthread_pollresults_closed','showthread_firstunread','showthread_nextnewestthread','showthread_nextoldestthread','forumrules','showthread','forumdisplay_loggedinuser','showthread_browsing','showthread_adds','error_nonextnewest','error_nonextoldest','error_invalidid','showthread','showthread_replybox','polloption_multiple','polloption','showthread_pollresults','pollresult','showthread_polloptions','gobutton','timezone','username_loggedout','username_loggedin','phpinclude','headinclude','header','footer','forumjumpbit','forumjump','nav_linkoff','nav_linkon','navbar','nav_joiner','pagenav','pagenav_curpage','pagenav_firstlink','pagenav_lastlink','pagenav_nextlink','pagenav_pagelink','pagenav_prevlink','home_microstats')

                            AND (templatesetid=-1 OR templatesetid='1'))

                          

                          

                          ORDER BY templatesetid

Time before: 0.050382018089294

Time after:  0.084350943565369



--------------------------------------------------------------------------------



Query: SELECT * FROM usergroup WHERE usergroupid=6

Time before: 0.099544048309326

Time after:  0.10035002231598



--------------------------------------------------------------------------------



Query: SELECT DISTINCT username,userid FROM post WHERE post.threadid='12267' ORDER BY username

Time before: 0.10181796550751

Time after:  0.10406005382538



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='1997'

Time before: 0.10439896583557

Time after:  0.10510694980621



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='1680'

Time before: 0.10552799701691

Time after:  0.10609400272369



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='4440'

Time before: 0.10655701160431

Time after:  0.10710597038269



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='3111'

Time before: 0.10748505592346

Time after:  0.10801994800568



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='2765'

Time before: 0.108402967453

Time after:  0.10892903804779



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='657'

Time before: 0.10930895805359

Time after:  0.1098370552063



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='2764'

Time before: 0.1104040145874

Time after:  0.11099600791931



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='1188'

Time before: 0.11138594150543

Time after:  0.11192405223846



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='3281'

Time before: 0.11230003833771

Time after:  0.11282598972321



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='4467'

Time before: 0.11320805549622

Time after:  0.11377000808716



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='494'

Time before: 0.11415600776672

Time after:  0.11464202404022



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='84'

Time before: 0.11498594284058

Time after:  0.1154899597168



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='37'

Time before: 0.11585402488708

Time after:  0.11640596389771



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='119'

Time before: 0.11679697036743

Time after:  0.11732399463654



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='4103'

Time before: 0.11770296096802

Time after:  0.1182450056076



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='164'

Time before: 0.11862301826477

Time after:  0.11915397644043



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='378'

Time before: 0.11953604221344

Time after:  0.1200670003891



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='1675'

Time before: 0.12045705318451

Time after:  0.12099599838257



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='118'

Time before: 0.12137305736542

Time after:  0.1218980550766



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='3012'

Time before: 0.1222779750824

Time after:  0.12281095981598



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='121'

Time before: 0.12318801879883

Time after:  0.12371003627777



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='207'

Time before: 0.12408602237701

Time after:  0.12461698055267



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='895'

Time before: 0.12499403953552

Time after:  0.12563300132751



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='27'

Time before: 0.12602996826172

Time after:  0.12655401229858



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='319'

Time before: 0.12693095207214

Time after:  0.12745797634125



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='36'

Time before: 0.12784397602081

Time after:  0.12837696075439



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='1973'

Time before: 0.12873101234436

Time after:  0.12920904159546



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='753'

Time before: 0.12954998016357

Time after:  0.13002002239227



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='1'

Time before: 0.13039803504944

Time after:  0.1309460401535



--------------------------------------------------------------------------------



Query: SELECT * FROM forum WHERE forumid='2'

Time before: 0.13298904895782

Time after:  0.13386297225952



--------------------------------------------------------------------------------



Query: SELECT *,INSTR(',2,1,-1,', CONCAT(',', forumid, ',') ) AS ordercontrol FROM forumpermission WHERE usergroupid=6 AND (forumid='2' OR forumid='1' OR forumid='-1') ORDER BY ordercontrol LIMIT 1

Time before: 0.13446199893951

Time after:  0.13530600070953



--------------------------------------------------------------------------------





Warning:  Cannot add header information - headers already sent by (output started at /home/virtual/site1/fst/var/www/html/xfan/forums/global.php:144) in /home/virtual/site1/fst/var/www/html/xfan/forums/admin/functions.php on line 1817

Query: SELECT * FROM forum WHERE forumid='1'

Time before: 0.13944602012634

Time after:  0.1403089761734



--------------------------------------------------------------------------------



Query: SELECT COUNT(*) AS posts FROM post WHERE post.threadid='12267' AND post.visible=1

Time before: 0.14183604717255

Time after:  0.14337003231049



--------------------------------------------------------------------------------



Query: 

    SELECT post.postid FROM post

    WHERE post.threadid='12267' AND post.visible=1

    ORDER BY dateline  LIMIT 0,15



Time before: 0.14374005794525

Time after:  0.14963698387146



--------------------------------------------------------------------------------



Query: 

SELECT

post.*,post.username AS postusername,post.ipaddress AS ip,user.*,userfield.*,icon.title as icontitle,icon.iconpath,

attachment.attachmentid,attachment.filename,attachment.visible AS attachmentvisible,attachment.counter

,avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline

FROM post

LEFT JOIN icon ON icon.iconid=post.iconid

LEFT JOIN user ON user.userid=post.userid

LEFT JOIN userfield ON userfield.userid=user.userid

LEFT JOIN avatar ON avatar.avatarid=user.avatarid

                       LEFT JOIN customavatar ON customavatar.userid=user.userid

LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid

WHERE post.postid IN (0,310960,310993,311000,311023,311030,311066,311068,311069,311074,311081,311119,311121,311138,311152,311156)

ORDER BY dateline 



Time before: 0.15048205852509

Time after:  0.15912103652954



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='postbit_editedreason' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.16151094436646

Time after:  0.16274797916412



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='postbit_plusimg' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.1635400056839

Time after:  0.16456401348114



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='postbit_minusimg' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.16528594493866

Time after:  0.1662780046463



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='warn_seelink' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.16720497608185

Time after:  0.16825604438782



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='warn_viewlink' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.16900599002838

Time after:  0.17004001140594



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='warn_warnlink' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.17075097560883

Time after:  0.17174601554871



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='postbit_donation' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.17332899570465

Time after:  0.17444097995758



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='postbit_deletepost' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.17702496051788

Time after:  0.17812395095825



--------------------------------------------------------------------------------



Query: SELECT smilietext,smiliepath FROM smilie

Time before: 0.17922699451447

Time after:  0.18034398555756



--------------------------------------------------------------------------------



Query: SELECT bbcodetag,bbcodereplacement,twoparams FROM bbcode

Time before: 0.18671703338623

Time after:  0.18752002716064



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='postbit_first' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.22557103633881

Time after:  0.22681105136871



--------------------------------------------------------------------------------



Query: 

SELECT s.userid, u.username FROM subscribethread s, user u WHERE u.userid=s.userid AND s.threadid='12267' ORDER BY u.username



Time before: 0.75135099887848

Time after:  0.75270402431488



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='showthread_replyopen' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.75443696975708

Time after:  0.75552999973297



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='showthread_searchthread' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.75658094882965

Time after:  0.75742495059967



--------------------------------------------------------------------------------



Query: SELECT usergroupid FROM user WHERE userid=1

Time before: 0.75819194316864

Time after:  0.75879096984863



--------------------------------------------------------------------------------



Query: SELECT template FROM template WHERE title='showthread_hideposts' AND (templatesetid=-1 OR templatesetid='1') ORDER BY templatesetid DESC LIMIT 1

Time before: 0.76006102561951

Time after:  0.76127099990845



--------------------------------------------------------------------------------



Query: 

        SELECT username,invisible,userid

        FROM user

        WHERE inthread=12267

        AND lastactivity>1036951798

        AND lastvisit<>lastactivity

    

Time before: 0.76354205608368

Time after:  0.7646050453186



--------------------------------------------------------------------------------



Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'1') ORDER BY replacementsetid DESC,replacementid DESC

Time before: 0.7908970117569

Time after:  0.79266202449799



--------------------------------------------------------------------------------





Page generated in 0.77495300769806 seconds with 62 queries,

spending 0.097008109092712 doing MySQL queries and 0.67794489860535 doing PHP things.

Query: UPDATE session SET lastactivity=1036952398,location='/xfan/forums/showthread.php?s=&threadid=12267&showqueries=1' WHERE sessionhash='85bbc2f6adbf444240a47dc0e7aea108' Time before: 0.97746205329895 Time after: 0.97822296619415 

--------------------------------------------------------------------------------

Query: UPDATE user SET lastactivity=1036952398,inforum='2',inthread='12267' WHERE userid='1' Time before: 0.97845804691315 Time after: 0.97900998592377 

--------------------------------------------------------------------------------

Query: UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='12267' Time before: 0.97922301292419 Time after: 0.97965598106384 

--------------------------------------------------------------------------------

Erwin · #25 11-10-2002, 03:03 AM

First of all, you can reduce your queries by 12 by including adding the following templates to your showthread.php $templatesused="" section.

Open showthread.php -

Find:

PHP Code:


			
if ($action=="showpost") {



    $templatesused = '

Replace with:

PHP Code:


			
if ($action=="showpost") {



    $templatesused = 'postbit_editedreason,postbit_plusimg,postbit_minusimg,warn_seelink,warn_viewlink,warn_warnlink,postbit_donation,postbit_deletepost,postbit_first,showthread_replyopen,showthread_searchthread,showthread_hideposts,

That's 12 queries that you save by caching these templates. You must not have installed them properly because the hack instructions tell you to do this in the first place - I know that my Search This Thread hack has instructions to do this.

Secondly, what is this?

PHP Code:


			
SELECT COUNT(*) AS postsc FROM post WHERE post.threadid='12267' AND userid='37'

This is repeated many times - it is a loop query. What is this counting?

X-Fan · #26 11-10-2002, 09:06 AM

I have *no* idea. Userid 37 is one of my site's long-time visitors.

Erwin · #27 11-10-2002, 09:35 AM

It's not just that userid - every single poster in that thread queries the database for a count as postsc - what is this postsc? Check your functions.php

X-Fan · #28 11-10-2002, 09:39 AM

Ah, that postsc is in showthread.php - it's part of the Sort-Order-Search in Thread View Hack by Madman

Erwin · #29 11-10-2002, 10:03 PM

Take that hack out and see if it makes a different - it's adding a query for each post in a thread - if you have 40 posts in a thread, that's 40 queries.

Smoothie · #30 11-10-2002, 11:58 PM

Quote:

Originally posted by X-Fan
Ah, that postsc is in showthread.php - it's part of the Sort-Order-Search in Thread View Hack by Madman

I used that hack when it first came out. The first time I downloaded it, it added like 30 more queries to showthread. I posted in his thread about this and Madman updated the hack to eliminate almost all but one extra query I think. I used the latest version, and it did add only one extra query, if i remember right. So, either the hack installed is not the latest version, or it was installed wrong.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.07898 seconds Memory Usage 2,412KB Queries Executed 11 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (4)bbcode_php (1)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (1)pagenav (1)pagenav_curpage (3)pagenav_pagelink (10)post_thanks_box (10)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (10)post_thanks_postbit_info (10)postbit (10)postbit_onlinestatus (10)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.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/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.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 showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!