Queries, Queries, Queries ..

[Sebastian]

Page generated in 0.36285806 seconds (83.35% PHP - 16.65% MySQL) with 18 queries.
[Debug Mode OFF] [PHP v4.2.3] [GZIP enabled - level 1]

I am curious what the average STOCK with 1 - 2 hacks board get.. I just upgraded to 229, I had a problem before because all I had was the welcome panel hack and I was getting 27 queries.. now I am getting 18 which is better, but I have still seen board with 14 - 15 queries WITH hacks! What gives? how are they getting 15 queries with several hacks and I have none and I am getting 18, there is obviously a little problem there :P

Also, before upgrading I was getting 97% PHP and 3% mysql .. now the sql percent doubled..

Can someone explain why is this?

[Xenon]

haven't looked at the vb229 code yet, perhaps something has been optimized....

Also: there are many methods out to reduce queries, try to search pm-queries total threads/posts and so on.

also if you don't want some things, you can reduce queries to, also with large hacked boards ;)

[Sebastian]

Thanks Xenon, I enabled debug mode to see queries.. vbproarcade is adding 3 queries to forum home when there isn't even anything vbproarcade related on forum home..
I have to asks Mr. vbproarcade :paranoid:


Query: SELECT * FROM arcadegames ORDER BY title ASC
Time before: 0.32712602615356
Time after: 0.3318430185318

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

Query: SELECT template FROM template WHERE title='vbproarcade_gamelinkbit' AND (templatesetid=-1 OR templatesetid=4) ORDER BY templatesetid DESC LIMIT 1
Time before: 0.33376896381378
Time after: 0.33636605739594

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

Query: SELECT template FROM template WHERE title='vbproarcade_gamelinks' AND (templatesetid=-1 OR templatesetid=4) ORDER BY templatesetid DESC LIMIT 1
Time before: 0.34161496162415
Time after: 0.3439689874649

[Xenon]

you can reduce the query ammount by adding vbproarcade_gamelinkbit and vbproarcade_gamelinks to the $templatesused variable ;)

[Sebastian]

sorry am a newbie, can you explain how? :confused:

[Xenon]

in your index.php you'll find something like that:

$templatesused='forumhome_birthdaybit,error_noperm ission,forumhome_pmloggedin,forumhome_welcometext, forumhome_logoutcode,forumhome_newposts,forumhome_ todayposts,forumhome_logincode,forumhome_loggedinu ser,forumhome_loggedinusers,forumhome_lastpostby,f orumhome_moderator,forumhome_forumbit_level1_nopos t,forumhome_forumbit_level1_post,forumhome_forumbi t_level2_nopost,forumhome_forumbit_level2_post,for umhome,forumhome_unregmessage';

just add the two names there

[Erwin]

Make sure all templates being eval() are cached in $templatesused in all your php files. :) That helps.

[Sebastian]

Thanks guys, I am down from 21 queries to 18, I am trying to go lower, lol .. how does this look? just trying to make sure nothing is looping :P

is it bad if I keep debug=1 in config.php so I could keep an eye on this page when I feel the need? or is there another easy method?


Query: SELECT template FROM template WHERE title='options'
Time before: 0.15240097045898
Time after: 0.15558695793152

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

Query: SELECT sessionhash,userid,host,useragent,styleid FROM session WHERE lastactivity>1037764842 AND sessionhash='c9f334daa0620212783908c9197b180f' AND (host='68.63.161.176' OR (althash='' AND althash<>'')) AND useragent='Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;'
Time before: 0.19247901439667
Time after: 0.19490694999695

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

Query: SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.userid='1'
Time before: 0.19714498519897
Time after: 0.20041298866272

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

Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='1'
Time before: 0.20493304729462
Time after: 0.20647895336151

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

Query: SELECT template,title
FROM template
WHERE (title IN ('forumhome_birthdaybit','error_nopermission','for umhome_pmloggedin','forumhome_welcometext','forumh ome_logoutcode','forumhome_newposts','forumhome_to dayposts','forumhome_logincode','forumhome_loggedi nuser','forumhome_loggedinusers','forumhome_lastpo stby','forumhome_moderator','forumhome_forumbit_le vel1_nopost','forumhome_forumbit_level1_post','for umhome_forumbit_level2_nopost','forumhome_forumbit _level2_post','forumhome','forumhome_unregmessage' ,'vbproarcade_gamelinkbit','vbproarcade_gamelinks' ,'gobutton','timezone','username_loggedout','usern ame_loggedin','phpinclude','headinclude','header', 'footer','forumjumpbit','forumjump','nav_linkoff', 'nav_linkon','navbar','nav_joiner','pagenav','page nav_curpage','pagenav_firstlink','pagenav_lastlink ','pagenav_nextlink','pagenav_pagelink','pagenav_p revlink','home_microstats')
AND (templatesetid=-1 OR templatesetid=4))
OR (title = 'maxloggedin')
OR (title = 'birthdays')
ORDER BY templatesetid
Time before: 0.20853698253632
Time after: 0.23826396465302

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

Query: SELECT * FROM usergroup WHERE usergroupid=6
Time before: 0.27794396877289
Time after: 0.28122901916504

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

Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1
Time before: 0.28548502922058
Time after: 0.28720498085022

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

Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND dateline>1037749782 AND folderid=0
Time before: 0.28879702091217
Time after: 0.29008603096008

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

Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND messageread=0 AND folderid=0
Time before: 0.29159295558929
Time after: 0.29285097122192

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

Query: SELECT COUNT(*) AS users,MAX(userid) AS max FROM user
Time before: 0.30035698413849
Time after: 0.30172097682953

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

Query: SELECT COUNT(*) AS posts FROM post
Time before: 0.303297996521
Time after: 0.30426096916199

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

Query: SELECT COUNT(*) AS threads FROM thread
Time before: 0.30569303035736
Time after: 0.30717396736145

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

Query: SELECT userid,username FROM user WHERE userid=24
Time before: 0.30870699882507
Time after: 0.31004095077515

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

Query: SELECT * FROM forum WHERE displayorder<>0 AND active=1 ORDER BY parentid,displayorder
Time before: 0.31353199481964
Time after: 0.31699800491333

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

Query: SELECT forumid,canview,canpostnew FROM forumpermission WHERE usergroupid='6'
Time before: 0.32239699363708
Time after: 0.32396697998047

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

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.3255490064621
Time after: 0.32686197757721

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

Query: SELECT COUNT(*) AS sessions FROM session WHERE userid=0 AND lastactivity>1037764843
Time before: 0.32852005958557
Time after: 0.3303279876709

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

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>1037764843
ORDER BY invisible ASC, username ASC
Time before: 0.3318909406662
Time after: 0.33439099788666

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

Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'4') ORDER BY replacementsetid DESC,replacementid DESC
Time before: 0.45747303962708
Time after: 0.46432197093964

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


Page generated in 0.4018360376358 seconds with 19 queries,
spending 0.070354580879211 doing MySQL queries and 0.33148145675659 doing PHP things.
Query: UPDATE session SET lastactivity=1037765742,location='/forums/index.php?s=&showqueries=1' WHERE sessionhash='c9f334daa0620212783908c9197b180f' Time before: 0.5302209854126 Time after: 0.53218805789948
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1037765742,inforum='0' WHERE userid='1' Time before: 0.53371500968933 Time after: 0.53505504131317
--------------------------------------------------------------------------------

[joor]

Super noob here, what is the debug=1 in config.php? I would like to try that.

:banana:

[Sebastian]

debug allows you to troubleshoot problems or when your trying to optimize things like me :)

in admin/config.php add $debug=1; on a new line, then go to your forums and on the page you want to debug add: &showqueries=1 or &explain=1 to the url .. for example, if you want to see the queries being performed in forum home you're url should look like: /forums/index.php?s=&showqueries=1

don't forget to remove it from the config once your done.

[joor]

Oh cool! I added the debug info but didn't know about the querystring to add. Just moved from a shared to a dedicated today, want to see my difference?

Shared Server:
Page generated in 1.614294052124 seconds with 21 queries,
spending 0.69835984706879 doing MySQL queries and 0.91593420505524 doing PHP things.

Dedicated Server:
Page generated in 0.10820305347443 seconds with 21 queries,
spending 0.029100894927979 doing MySQL queries and 0.079102158546448 doing PHP things

Woot! Thanks, I didn't want to install the vbMicrostats to get that info. So now I have your question, is it safe to leave that debug in config?

[Erwin]

No. :) It is a security hole that hackers can take advantage of. This is why from vB 2.2.4 upwards it was disabled as standard.

[Xenon]

also with debug mode you can change the gloabal template set and other inner vb-things, which can be very risky, too if you don't know what you do ;)

[Dean C]

Geez 18 queries...

I have 48 on my forumhome..

- miSt

[Sebastian]

yeah, and I want it lower lol ;)

[N9ne]

48 on forumhome? Mist, you're killing your server :eek:

[Sebastian]

Forum home usually performs more queries than any other page .. 48 is a tad high, I've seen some boards over 250! it makes me very nervous :paranoid:

they obviously do not know what the heck they are doing and don't know how to manage their forums for that matter.

[Dean C]

Well i do know what im doing..

But my server can cope very well...

Im going to check my file tonight for any misplaced template calls that aren't in the $templatesused variable :)

- miSt

[Xenon]

if you have questions, just ask ;)

there are some guys out here who likes to optimize queries ;)

[N9ne]

I'm one of them, but TECK is the god of query optimization!

Anyway...I definitely think anything above 35 on forumhome is not good...I, on my "version 2" of my forum, will have 23 queries logged out, and 25 logged in :)

The only problems I have are with showthread, as there is much to be put there.

[Dean C]

Mind if i send my index.php your way Xenon?

I had a quick go tonite and only cut down one query strangly...

- miSt

[Sebastian]

POST IT! We all want to see/help :)

[NTLDR]

Originally posted by Sebastian
POST IT! We all want to see/help :)

You are not allowed to post complete vB here ;)

[Sebastian]

Originally posted by NTLDR
You are not allowed to post complete vB here ;)
Thanks for reminding me, I forgot :rolleyes:

[Xenon]

mist, yes you can send it to me, then i'll look at it and try to find some things ;)

but don't expect too much too fast. i've got a lot of work ;)