View Full Version : 67 queries?!
X-Fan
10-25-2002, 03:51 PM
G'day all,
Hoping someone can help me nail down why my showthread.php is generating 67 queries!
Take a look at this page:
http://x-mencomics.com/xfan/forums/showthread.php?s=&threadid=12267
I've ensured that all templates used are listed, and it's still coming up as 67 queries.
Any ideas?
NTLDR
10-25-2002, 03:56 PM
Only thing I can think of that could be buming up there queries is the flags hack, I've never used it so I can't say and without having a list of the queries that are being run on your site its hard to tell ;)
X-Fan
10-25-2002, 04:00 PM
I've had the flags hack installed for a while now, and it hasn't had a noticeable impact on query totals.
However, the expand/contract posts hack appears to be using a sizeable chunk, as I've only just recently installed that.
NTLDR
10-25-2002, 04:03 PM
I never had any problems with that hack when I had it installed.
The best option is this:
Note leave this line in congif.php for as little time as posible, it will compromise security.
Add to config.php $debug=1;
Then visit:
http://x-mencomics.com/xfan/forums/showthread.php?s=&threadid=12267&showqueries=1
This will give a list of queries that are being performed on showthread and you can look for a whole batch that are similar, or you could save the HTML and post it here.
Remember to remove $debug=1; from config.php
X-Fan
10-25-2002, 04:19 PM
Okay, this is getting really weird. I just had to edit the first post of the thread I linked to in my previous post above, and now it's up to 74 queries!
How's that possible? (PHP newbie here, if you couldn't guess! :p )
EDIT: Okay, I did the debug, and I'm seeing numerous copies of this one query:
Query: SELECT usergroupid FROM user WHERE userid=1
userid 1 is my userid at my site, and that's showing up around 15 times on that debug list. Is it meant to be that many?
NTLDR
10-25-2002, 04:23 PM
No, that should be there only once IMO, check in showthread.php and in the getpostbit(); function in admin/functions.php and see if you have that query in there multiple times:
SELECT usergroupid FROM user WHERE userid=
^^ it will contain that part.
NTLDR
10-25-2002, 04:24 PM
Also, I don't no if any extra stuff appears if you logged in or not, here what I get:
Page generated in 1.02646506 seconds (83.87% PHP - 16.13% MySQL) with 69 queries.
Which is still way too high.
X-Fan
10-25-2002, 11:46 PM
I just checked both showthread.php and admin/functions.php, and that SELECT query only appears once in admin/functions.php under the "Start getpermissions" section.
But that query is still being called numerous times on the showthread page.
Any ideas why?
Erwin
10-26-2002, 12:30 AM
67 queries is shocking. No wonder your server is choking.
You've got a hack in your functions.php that finds out if a post is by you (userid=1) and then if it is you have ads under your username in the postbit.
That's the hack that's the reason for your userid being queried like that possibly - the flags hack does not add more queries at all.
X-Fan
10-26-2002, 12:34 AM
Erwin, there's nothing in functions.php that checks for userid=1 specifically. There's only that one line that selects the usergroupid from the user table, and I'm assuming that's to check whether the user has permissions to view the thread.
Erwin
10-26-2002, 05:57 AM
But it should not be a query - all you need is a line that says "if ($bbuserinfo[userid]==1) { etc. etc. " which does not take a db query at all. What other hacks do you have? You may have installed a hack, and uninstalled it, but left some code in your showthread.php or functions.php.
X-Fan
10-26-2002, 11:58 AM
There's no such line in my showthread.php or admin/functions.php
And I've got a fair few hacks installed, but I've completely removed all code from any hacks that I've uninstalled. I always ensure to double-check that so as to not chance any errors.
Tigga
11-03-2002, 06:23 AM
Do you by chance have TECK's "Quick Post Delete" (or something like that) hack installed? If so check the thread for updates. There was an error that was causing many queries on the showthread pages.
X-Fan
11-03-2002, 12:50 PM
Ah, many thanks PP! I do indeed have that hack installed, as it's immensely handy. I'll head over to the thread for it now and get the update.
Tigga
11-03-2002, 01:10 PM
I thought that might be it. ;) I had the hack installed on my board and noticed the jump in queries about a month later. It took me forever to figure out what was causing it. And I agree... It's very handy and much easier than the default method. :)
X-Fan
11-03-2002, 01:23 PM
Hmmm, even with the fix I'm still getting around 50 queries on a showthread page, like this one...
http://x-mencomics.com/xfan/forums/showthread.php?s=&threadid=12431&perpage=15&display=&pagenumber=2
Tigga
11-03-2002, 01:29 PM
Hmmm... That's odd. What are the results if you do an explain=1 on the page now?
X-Fan
11-03-2002, 01:41 PM
Lots of select count(*) and select template queries.
Tigga
11-03-2002, 01:56 PM
Have you added all the templates for hacks in the $templatesused section near the top of the file? It sounds like you might have missed a few. The 1st and 7th table you see on the explain=1 should be the only ones that start out with template. If you see any others that look like this...
Query: SELECT template FROM template WHERE title='some_template_name_here'
Just add the name of that template in $templatesused on showthread.php and it'll remove at least a few of the queries. I only have one select count(*) query on my page, so I'm not sure where those are coming from. If you post a couple of them here I'll try to help you figure that out if you like.
Oh, a note about adding the templates... Make sure you add them under the 2nd instance of $templatesused on showthread.php. You should see the following lines right above it...
// ################################################## ############################
// ############################# SHOW THREAD ####################################
// ################################################## ############################
Erwin
11-03-2002, 06:14 PM
Plurplanet is right. Each template that is eval() that is not in the $templatesused list is an extra unnecessary query. Cache them by adding them to the $templatesused section.
X-Fan
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
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
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
11-10-2002, 01:20 AM
Okay, here we go... from the following thread:
http://x-mencomics.com/xfan/forums/showthread.php?s=&threadid=12267&showqueries=1
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','post bit_buddy','postbit_useremail','icq','aim','yahoo' ,'postbit_homepage','postbit_profile','postbit_ip_ show','postbit_ip_hidden','postbit','postbit_sendp m','postbit_avatar','postbit_offline','postbit_onl ine','postbit_editedby','postbit_signature','postb it_attachment','postbit_attachmentimage','showthre ad_adminoptions','showthread_threadrate','showthre ad_pollresults_voted','showthread_pollresults_clos ed','showthread_firstunread','showthread_nextnewes tthread','showthread_nextoldestthread','forumrules ','showthread','forumdisplay_loggedinuser','showth read_browsing','showthread_adds','error_nonextnewe st','error_nonextoldest','error_invalidid','showth read','showthread_replybox','polloption_multiple', 'polloption','showthread_pollresults','pollresult' ,'showthread_polloptions','gobutton','timezone','u sername_loggedout','username_loggedin','phpinclude ','headinclude','header','footer','forumjumpbit',' forumjump','nav_linkoff','nav_linkon','navbar','na v_joiner','pagenav','pagenav_curpage','pagenav_fir stlink','pagenav_lastlink','pagenav_nextlink','pag enav_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,attach ment.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,31106 8,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='1226 7' 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
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:
if ($action=="showpost") {
$templatesused = '
Replace with:
if ($action=="showpost") {
$templatesused = 'postbit_editedreason,postbit_plusimg,postbit_minu simg,warn_seelink,warn_viewlink,warn_warnlink,post bit_donation,postbit_deletepost,postbit_first,show thread_replyopen,showthread_searchthread,showthrea d_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?
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
11-10-2002, 09:06 AM
I have *no* idea. Userid 37 is one of my site's long-time visitors.
Erwin
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
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
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
11-10-2002, 11:58 PM
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.
KelteN
11-11-2002, 06:52 AM
Weird :\
X-Fan
11-11-2002, 09:25 PM
Updated Madman's hack and it's down to 31 queries now.
Erwin
11-11-2002, 09:47 PM
There you go!!! That's a lot better! :)
Bison
11-15-2002, 05:51 PM
I hate to jump in but i'm having simular issues with my index page. this line appears several times which has jumped my queries on my index page to a shocking 90 queries!
Query: SELECT template FROM template WHERE title='options'
Time before: 0.028990030288696
Time after: 0.030089020729065
Query: SELECT sessionhash,userid,host,useragent,styleid FROM session WHERE lastactivity>1037380414 AND sessionhash='57565ca59c79463a10c80c458c712492' AND host='24.52.238.196' AND useragent='Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;'
Time before: 0.036381006240845
Time after: 0.036906003952026
Query: SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.userid='1'
Time before: 0.037148952484131
Time after: 0.038058996200562
Query: UPDATE user SET timeonline=timeonline+214 WHERE userid='1'
Time before: 0.038669943809509
Time after: 0.038979053497314
Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='24'
Time before: 0.03931999206543
Time after: 0.039639949798584
Query: SELECT template,title
FROM template
WHERE (title IN ('forumhome_birthdaybit','error_nopermission','for umhome_pmloggedin','forumhome_ welcometext','forumhome_logoutcode','forumhome_new posts','forumhome_todayposts', 'forumhome_logincode','forumhome_todayloggedinuser ','forumhome_todayloggedinuser s','forumhome_loggedinuser','forumhome_loggedinuse rs','forumhome_lastpostby','fo rumhome_moderator','forumhome_forumbit_level1_nopo st','forumhome_forumbit_level1 _post','forumhome_forumbit_level2_nopost','forumho me_forumbit_level2_post','foru mhome','forumhome_unregmessage','gobutton','timezo ne','username_loggedout','user name_loggedin','phpinclude','headinclude','header' ,'footer','forumjumpbit','foru mjump','nav_linkoff','nav_linkon','navbar','nav_jo iner','pagenav','pagenav_curpa ge','pagenav_firstlink','pagenav_lastlink','pagena v_nextlink','pagenav_pagelink' ,'pagenav_prevlink','home_microstats')
AND (templatesetid=-1 OR templatesetid='19'))
OR (title = 'maxloggedin')
OR (title = 'birthdays')
ORDER BY templatesetid
Time before: 0.0398850440979
Time after: 0.053624987602234
Query: SELECT COUNT(*) AS count FROM session WHERE userid=0 AND lastactivity>1037380414
Time before: 0.056285977363586
Time after: 0.056926965713501
Query: SELECT COUNT(DISTINCT(userid)) AS count FROM session WHERE session.userid>0 AND session.lastactivity>1037380414
Time before: 0.05706000328064
Time after: 0.057842969894409
Query: SELECT * FROM usergroup WHERE usergroupid=6
Time before: 0.060628056526184
Time after: 0.061303973197937
Query: SELECT template
FROM template
WHERE title='memberoftheday'
AND templatesetid=-2
Time before: 0.064283013343811
Time after: 0.06466805934906
Query: SELECT user.avatarid,avatarpath,NOT ISNULL(avatardata) AS hascustom,customavatar.dateline
FROM user
LEFT JOIN avatar ON avatar.avatarid=user.avatarid
LEFT JOIN customavatar ON customavatar.userid=user.userid
WHERE user.userid='1'
Time before: 0.064878940582275
Time after: 0.066051959991455
Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1
Time before: 0.066372036933899
Time after: 0.066789031028748
Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND dateline>1037344130 AND folderid=0
Time before: 0.066923022270203
Time after: 0.067540049552917
Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND messageread=0 AND folderid=0
Time before: 0.067765951156616
Time after: 0.068228006362915
Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND folderid=0
Time before: 0.068578958511353
Time after: 0.069056034088135
Query: SELECT COUNT(*) AS users,MAX(userid) AS max FROM user
Time before: 0.070255994796753
Time after: 0.070462942123413
Query: SELECT COUNT(*) AS users,MAX(userid) AS max FROM user WHERE posts=0
Time before: 0.070605993270874
Time after: 0.072136998176575
Query: SELECT COUNT(*) AS users,MAX(userid) FROM user WHERE posts=1
Time before: 0.072307944297791
Time after: 0.073686003684998
Query: SELECT COUNT(*) AS users,MAX(userid) AS max FROM user WHERE joindate
Query: SELECT userid,username,posts FROM user ORDER BY posts DESC LIMIT 10
Time before: 0.075706005096436
Time after: 0.077715039253235
Query: SELECT DISTINCT(userid),COUNT(postid) AS posts FROM post WHERE userid > 0 AND post.dateline>=1037303014 GROUP BY userid ORDER BY posts DESC LIMIT 10
Time before: 0.078215003013611
Time after: 0.14599597454071
Query: SELECT username FROM user WHERE userid=142
Time before: 0.14624905586243
Time after: 0.1465630531311
Query: SELECT username FROM user WHERE userid=173
Time before: 0.14674603939056
Time after: 0.1471780538559
Query: SELECT username FROM user WHERE userid=164
Time before: 0.14748001098633
Time after: 0.14765405654907
Query: SELECT username FROM user WHERE userid=71
Time before: 0.1478099822998
Time after: 0.14804303646088
Query: SELECT username FROM user WHERE userid=132
Time before: 0.14824604988098
Time after: 0.14847004413605
Query: SELECT username FROM user WHERE userid=5
Time before: 0.14866399765015
Time after: 0.14887797832489
Query: SELECT username FROM user WHERE userid=16
Time before: 0.14907598495483
Time after: 0.14929699897766
Query: SELECT username FROM user WHERE userid=114
Time before: 0.14949202537537
Time after: 0.14970302581787
Query: SELECT username FROM user WHERE userid=40
Time before: 0.14989900588989
Time after: 0.15011298656464
Query: SELECT username FROM user WHERE userid=179
Time before: 0.15030801296234
Time after: 0.15053200721741
Query: SELECT template FROM template WHERE title='g_statistics' AND (templatesetid=-1 OR templatesetid='19') ORDER BY templatesetid DESC LIMIT 1
Time before: 0.15075099468231
Time after: 0.15162599086761
Query: SELECT username,userid,timeonline FROM user WHERE userid > 1 ORDER BY timeonline DESC LIMIT 1
Time before: 0.15185904502869
Time after: 0.15404903888702
Query: SELECT postid AS posts FROM post ORDER BY postid DESC LIMIT 1
Time before: 0.15432798862457
Time after: 0.15449500083923
Query: SELECT threadid AS threads FROM thread ORDER BY threadid DESC LIMIT 1
Time before: 0.15462899208069
Time after: 0.15504205226898
Query: SELECT userid,username FROM user WHERE userid=181
Time before: 0.15522801876068
Time after: 0.15545797348022
Query: SELECT COUNT(*) AS threads FROM thread WHERE lastpost > '1037344130'
Time before: 0.15562796592712
Time after: 0.15971195697784
Query: SELECT count(*) AS posts FROM post WHERE dateline > '1037344130'
Time before: 0.15988099575043
Time after: 0.19722998142242
Query: SELECT style.title, user.styleid, COUNT(*) AS count FROM user LEFT JOIN style USING (styleid) WHERE style.styleid IS NOT NULL AND style.userselect=1 GROUP BY user.styleid
Time before: 0.19739603996277
Time after: 0.20033705234528
Query: SELECT template FROM template WHERE title='forumhome_dropdownbit' AND (templatesetid=-1 OR templatesetid='19') ORDER BY templatesetid DESC LIMIT 1
Time before: 0.20062303543091
Time after: 0.20155501365662
Query: SELECT * FROM forum WHERE displayorder<>0 AND active=1 ORDER BY parentid,displayorder
Time before: 0.20349204540253
Time after: 0.20521998405457
Query: SELECT forumid,canview,canpostnew FROM forumpermission WHERE usergroupid='6'
Time before: 0.21054005622864
Time after: 0.21099603176117
Query: SELECT forumid,accessmask FROM access WHERE userid='1'
Time before: 0.21127700805664
Time after: 0.21157097816467
Query: SELECT user.userid,user.username,moderator.forumid
FROM moderator
LEFT JOIN user
ON (moderator.userid=user.userid)
ORDER BY user.username
Time before: 0.21170401573181
Time after: 0.21375501155853
Query: SELECT COUNT(*) AS sessions FROM session WHERE userid=0 AND lastactivity>1037380415
Time before: 0.21618103981018
Time after: 0.21690499782562
Query: SELECT COUNT(*) AS sessions FROM session WHERE userid>0 AND lastactivity>1037380415
Time before: 0.21703994274139
Time after: 0.2175829410553
Query: SELECT DISTINCT session.userid,username,invisible,usergroupid
FROM session
LEFT JOIN user ON (user.userid=session.userid)
WHERE session.userid>0 AND session.lastactivity>1037380415
ORDER BY invisible ASC, username ASC
Time before: 0.21829295158386
Time after: 0.21945405006409
Query: SELECT userid, username, usergroupid, lastactivity, invisible FROM user
WHERE lastactivity > 1037343600
ORDER BY username
Time before: 0.22246098518372
Time after: 0.22455894947052
Query:
SELECT COUNT(*) AS count, inforum
FROM user
WHERE lastactivity>1037380414
AND lastvisit<>lastactivity
GROUP BY inforum
Time before: 0.23457598686218
Time after: 0.236083984375
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=3
Time before: 0.23655498027802
Time after: 0.23730397224426
Query: SELECT template FROM template WHERE title='forumhome_newforumthreads' AND (templatesetid=-1 OR templatesetid='19') ORDER BY templatesetid DESC LIMIT 1
Time before: 0.23760294914246
Time after: 0.23877704143524
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=4
Time before: 0.24047100543976
Time after: 0.24087905883789
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=53
Time before: 0.24284100532532
Time after: 0.24322700500488
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=35
Time before: 0.24459004402161
Time after: 0.24491202831268
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=20
Time before: 0.24664103984833
Time after: 0.2471729516983
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=37
Time before: 0.24912095069885
Time after: 0.24941897392273
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=38
Time before: 0.2509800195694
Time after: 0.25136005878448
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=26
Time before: 0.25331604480743
Time after: 0.25352001190186
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=27
Time before: 0.25516498088837
Time after: 0.25558400154114
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=28
Time before: 0.25710201263428
Time after: 0.25764095783234
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=29
Time before: 0.25941598415375
Time after: 0.2599960565567
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=1
Time before: 0.26179897785187
Time after: 0.26199495792389
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=2
Time before: 0.26357901096344
Time after: 0.26453304290771
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=33
Time before: 0.26654398441315
Time after: 0.26704895496368
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=32
Time before: 0.26881396770477
Time after: 0.27069997787476
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=5
Time before: 0.27237796783447
Time after: 0.27317702770233
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=44
Time before: 0.27496600151062
Time after: 0.27517104148865
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=34
Time before: 0.27671599388123
Time after: 0.27701604366302
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=6
Time before: 0.27877295017242
Time after: 0.27897298336029
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=7
Time before: 0.28059005737305
Time after: 0.28095495700836
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=40
Time before: 0.28271305561066
Time after: 0.28316497802734
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=21
Time before: 0.28473496437073
Time after: 0.28509998321533
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=30
Time before: 0.28676199913025
Time after: 0.28702199459076
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=8
Time before: 0.28881895542145
Time after: 0.28944098949432
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=10
Time before: 0.29133296012878
Time after: 0.29168200492859
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=39
Time before: 0.29352200031281
Time after: 0.29386496543884
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=9
Time before: 0.29544997215271
Time after: 0.29576897621155
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=11
Time before: 0.29733800888062
Time after: 0.29765200614929
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=12
Time before: 0.29985797405243
Time after: 0.30007195472717
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=13
Time before: 0.30181300640106
Time after: 0.30256700515747
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=43
Time before: 0.30449604988098
Time after: 0.30477404594421
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=14
Time before: 0.3062299489975
Time after: 0.30673694610596
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=15
Time before: 0.308434009552
Time after: 0.30870294570923
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=17
Time before: 0.31029105186462
Time after: 0.31058895587921
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=16
Time before: 0.31226694583893
Time after: 0.31331396102905
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=18
Time before: 0.31527101993561
Time after: 0.31547796726227
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=19
Time before: 0.31704497337341
Time after: 0.32025003433228
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=36
Time before: 0.32246005535126
Time after: 0.32312798500061
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=23
Time before: 0.32481598854065
Time after: 0.32508099079132
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=25
Time before: 0.32689094543457
Time after: 0.32720601558685
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=24
Time before: 0.32884502410889
Time after: 0.32937395572662
Query: SELECT COUNT(threadid) AS total FROM thread WHERE dateline>1037344130 and forumid=22
Time before: 0.33109605312347
Time after: 0.33147799968719
Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'20') ORDER BY replacementsetid DESC,replacementid DESC
Time before: 0.34253704547882
Time after: 0.34418594837189
Erwin
11-16-2002, 03:41 AM
90 queries??? That is unacceptable.
You can add the templates called "options" and "memberoftheday" to your templatesused variable at the top to be cached.
Then remove the hack from your index.php that is making a loop causing a query for every forum you have that counts the number of threads per forum after a certain date - take that thread out.
at forumhome i have 62 Queries, but in some big threads i have over 300 Queries and the biggest i ever saw was over 500! :paranoid:
Erwin
11-19-2002, 01:25 AM
62 queries is still too high. Keep it to less than 30 if you can. Don't install hacks that does a query for each forum you have.
what is the best thing to do, so i become it to 30? ;)
Erwin
11-19-2002, 04:02 AM
I don't know what you have done to index.php so I can't tell you. Read this thread and do a search to learn about optimizing your board.
vBulletin® v3.8.12 by vBS, Copyright ©2000-2025, vBulletin Solutions Inc.