vb.org Archive - Queries, Queries, Queries ..

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - Queries, Queries, Queries .. (https://vborg.vbsupport.ru/showthread.php?t=45903)

Queries, Queries, Queries ..

Quote:

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?

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 ;)

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:

Quote:

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

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

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

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

Code:

$templatesused='forumhome_birthdaybit,error_nopermission,forumhome_pmloggedin,forumhome_welcometext,forumhome_logoutcode,forumhome_newposts,forumhome_todayposts,forumhome_logincode,forumhome_loggedinuser,forumhome_loggedinusers,forumhome_lastpostby,forumhome_moderator,forumhome_forumbit_level1_nopost,forumhome_forumbit_level1_post,forumhome_forumbit_level2_nopost,forumhome_forumbit_level2_post,forumhome,forumhome_unregmessage';

just add the two names there

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

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?

Code:

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','forumhome_pmloggedin','forumhome_welcometext','forumhome_logoutcode','forumhome_newposts','forumhome_todayposts','forumhome_logincode','forumhome_loggedinuser','forumhome_loggedinusers','forumhome_lastpostby','forumhome_moderator','forumhome_forumbit_level1_nopost','forumhome_forumbit_level1_post','forumhome_forumbit_level2_nopost','forumhome_forumbit_level2_post','forumhome','forumhome_unregmessage','vbproarcade_gamelinkbit','vbproarcade_gamelinks','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=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 

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

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

:banana:

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.

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?

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.

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 ;)

Geez 18 queries...

I have 48 on my forumhome..

- miSt

yeah, and I want it lower lol ;)

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

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.

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

if you have questions, just ask ;)

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

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.

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

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

- miSt

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

Quote:

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

You are not allowed to post complete vB here ;)

Quote:

Originally posted by NTLDR
You are not allowed to post complete vB here ;)

Thanks for reminding me, I forgot :rolleyes:

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 ;)

Phrase Groups Available:

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