vb.org Archive - Lil help with queries would be greatly appreciated

heh well as most people on here I am having some probelms with queries... recently the admin that was taking care of hacks for our board etc. left, so basically we're kinda stuck with a whole bunch of queries slowing down the server, the problem is in the forumhome page not showthread or anything else, our forumhome page seems to be loading the slowest, this is really pissing me off as I am not sure about all the hacks installed and which ones can be optimized, it has steadily been getting worse since we're getting more and more users online at once, it has almost become unbearable lately when we get 700+ users online at once, I was always more of an IPB person and with this admin leaving I sort of got left with some of the dirty work O.o

anyways I'm posting the debug page, I'd appreciate it if anyone here could help me cut down at least a few queries on it

Code:

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

Time before: 0.0269079208374

Time after:  0.0274391174316



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



Query: SELECT sessionhash,userid,host,useragent,styleid FROM session WHERE lastactivity>1084301771 AND sessionhash='d9b63955b23fba096d879a7b3818cf96' AND host='24.80.170.210' AND useragent='Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;'

Time before: 0.0316779613495

Time after:  0.0320470333099



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



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

Time before: 0.0323219299316

Time after:  0.033185005188



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



Query: SELECT * FROM afterburner_stat WHERE time='1084226400'

Time before: 0.034087896347

Time after:  0.0347929000854



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



Query: select * FROM afterburner_sessions_day WHERE userid='8'

Time before: 0.0350360870361

Time after:  0.0363850593567



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



Query: SELECT COUNT(*) as anzahl FROM session WHERE lastactivity >= '1084301771'

Time before: 0.0366110801697

Time after:  0.0372800827026



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



Query: DELETE FROM afterburner_sessions_day WHERE time<'1084226400'

Time before: 0.0374920368195

Time after:  0.0390110015869



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



Query: select * FROM afterburner_sessions_day WHERE userid='8'

Time before: 0.0391459465027

Time after:  0.0404651165009



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



Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='1'

Time before: 0.0406899452209

Time after:  0.0410270690918



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



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','hl_statistics','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','vbpopup')

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

                          OR (title = 'maxloggedin')

                          OR (title = 'birthdays')

                          ORDER BY templatesetid

Time before: 0.0413138866425

Time after:  0.0548939704895



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



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

Time before: 0.0599300861359

Time after:  0.0607349872589



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



Query: SELECT * FROM usergroup WHERE usergroupid=6

Time before: 0.0616159439087

Time after:  0.0621058940887



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



Query: SHOW TABLE STATUS

Time before: 0.0631830692291

Time after:  0.0751860141754



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



Query: 

        SELECT privatemessageid, messageread, dateline, folderid

        FROM privatemessage

        WHERE userid = 8 AND fromuserid NOT IN (37607)

Time before: 0.0797200202942

Time after:  0.0813300609589



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



Query: SELECT COUNT(*) AS todayreg FROM user WHERE joindate > 1084226400

Time before: 0.083251953125

Time after:  0.668168067932



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



Query: SELECT userid,username,posts FROM user ORDER BY posts DESC LIMIT 5

Time before: 0.668404102325

Time after:  1.37540102005



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



Query: SELECT userid,posts,username,joindate FROM user ORDER BY joindate DESC LIMIT 5

Time before: 1.37581706047

Time after:  1.90660810471



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



Query: SELECT thread.replycount,thread.threadid,thread.lastpost,thread.lastposter,thread.views,thread.title,user.userid FROM thread LEFT JOIN user ON (user.username=thread.lastposter) WHERE forumid NOT IN(26,27,69,82,86,113) ORDER BY lastpost DESC LIMIT 5

Time before: 1.90704011917

Time after:  2.03565001488



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



Query: SELECT userid, username FROM user ORDER BY joindate DESC LIMIT 1

Time before: 2.03707790375

Time after:  2.65010595322



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



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: 2.65030097961

Time after:  3.58653306961



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



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

Time before: 3.58675289154

Time after:  3.58739495277



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



Query: SELECT * FROM forum WHERE displayorder<>0 AND active=1 ORDER BY parentid,displayorder

Time before: 3.59092092514

Time after:  3.59282898903



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



Query: SELECT forumid,canview,canpostnew FROM forumpermission WHERE usergroupid='6'

Time before: 3.59972810745

Time after:  3.60013508797



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



Query: SELECT user.userid,user.username,moderator.forumid

                                  FROM moderator

                                  LEFT JOIN user

                                    ON (moderator.userid=user.userid)

                                  ORDER BY user.username

Time before: 3.60035610199

Time after:  3.60583305359



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



Query: SELECT COUNT(*) AS sessions FROM session WHERE userid=0 AND lastactivity>1084301775

Time before: 3.60757303238

Time after:  3.60820388794



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



Query: SELECT session.useragent FROM session WHERE userid=0 AND lastactivity>1084301775

Time before: 3.60838603973

Time after:  3.60939598083



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



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>1084301775

                              ORDER BY invisible ASC, username ASC

Time before: 3.65076899529

Time after:  3.65990400314



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



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

Time before: 3.74667406082

Time after:  3.75431799889



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





Page generated in 3.73481702805 seconds with 28 queries,

spending 3.56357741356 doing MySQL queries and 0.171239614487 doing PHP things.

Query: UPDATE session SET lastactivity=1084302671,location='/mpc/forum/index.php?&showqueries=1' WHERE sessionhash='d9b63955b23fba096d879a7b3818cf96' Time before: 3.77678203583 Time after: 3.77711391449 

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

Query: UPDATE user SET lastactivity=1084302671,inforum='0' WHERE userid='8' Time before: 3.77724504471 Time after: 3.77809309959 

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

I'm thinking that perhaps some some queries can be added to the $templatesused= part of index.php but I just wanted to make sure which ones I can safely add

This is the current $templatesused= we have in our index.php

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,vbproarcade_gamelinkbit,vbproarcade_gamelinks';

$templatesused .=',hl_statistics';

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01134 seconds Memory Usage 1,809KB 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 (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: