|
67 queries?!
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/s...threadid=12267 I've ensured that all templates used are listed, and it's still coming up as 67 queries. Any ideas? |
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 ;)
|
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. |
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/s...&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 |
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: PHP Code:
|
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:
[sql]SELECT usergroupid FROM user WHERE userid=[/sql] ^^ it will contain that part. |
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. |
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? |
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. |
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.
|
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.
|
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. |
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.
|
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.
|
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. :)
|
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/s...=&pagenumber=2 |
Hmmm... That's odd. What are the results if you do an explain=1 on the page now?
|
Lots of select count(*) and select template queries.
|
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 #################################### // ################################################## ############################ |
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.
|
Yep, all evaluated templates are in the $templatesused section, and the page is still generating around 50 queries. I'm stumped.
|
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. |
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.
|
Okay, here we go... from the following thread:
http://x-mencomics.com/xfan/forums/s...&showqueries=1 PHP Code:
|
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:
PHP Code:
Secondly, what is this? PHP Code:
|
I have *no* idea. Userid 37 is one of my site's long-time visitors.
|
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
|
Ah, that postsc is in showthread.php - it's part of the Sort-Order-Search in Thread View Hack by Madman
|
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.
|
Quote:
|
Weird :\
|
Updated Madman's hack and it's down to 31 queries now.
|
There you go!!! That's a lot better! :)
|
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 |
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:
|
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? ;)
|
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.
|
nevermind, user error :)
|
| All times are GMT. The time now is 09:20 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 | |
|---|---|
|
|
 More Information |
|
|
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|