View Full Version : SQL Load on Server is increasing badly
SaN-DeeP
06-24-2004, 06:52 AM
I have been noting this since last 1 week, load on forums have gone very high. and it take a lot of time for a thread to get displayed, or whos online list or logging onto forums homepages properly.
here is the Online Users Load:
Page generated in 0.33739090 seconds (39.90% PHP - 60.10% MySQL) with 19 queries
showthread.php?p=34466#post34466 Load:
Page generated in 0.25890899 seconds (55.15% PHP - 44.85% MySQL) with 18 queries
Forums index.php Load:
Page generated in 0.28824997 seconds (29.54% PHP - 70.46% MySQL) with 14 queries
This load increases a lot when there are more then 10 users connected @ one time, and makes the entire forums go slow.
Can you please help me fix this.
And there are always more then 25 users :S
Need some help to fix this off please :(
Regards,
Sandy...
Boofo
06-24-2004, 07:05 AM
What hacks are you running on your forumhome that give you that many queries? I would start there.
SaN-DeeP
06-25-2004, 01:09 AM
i enabled the debug mode in config.php and now check my index.php load
Page generated in 0.19459200 seconds (46.26% PHP - 53.74% MySQL) with 20 queries wierd
here is the explanation of index.php
Connecting to database
Time before: 0.001802921295166
Time after: 0.0022330284118652
Time taken: 0.00043010711669922
--------------------------------------------------------------------------------
Query:
SELECT title, data FROM vb3_datastore
WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'memberoftheday', 'userstats', 'birthdaycache', 'maxloggedin', 'iconcache', 'eventcache', 'mailqueue')
Time before: 0.0023479461669922
Time after: 0.0027229785919189
Time taken: 0.00037503242492676
table type possible_keys key key_len ref rows Extra
vb3_datastore range PRIMARY PRIMARY 15 13 Using where
--------------------------------------------------------------------------------
Parsing functions.php
Time before: 0.006195068359375
Time after: 0.0094931125640869
Time taken: 0.0032980442047119
--------------------------------------------------------------------------------
Processing sessions.php
Time before: 0.009800910949707
Query:
SELECT *
FROM vb3_session
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
AND lastactivity > 1088125372
AND host = '203.115.83.97'
AND idhash = 'b00220156f5ad4123395dfe1da00e9e3'
Time before: 0.010317087173462
Time after: 0.010741949081421
Time taken: 0.00042486190795898
table type possible_keys key key_len ref rows Extra
vb3_session const PRIMARY PRIMARY 32 const 1
--------------------------------------------------------------------------------
Query:
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_holiday AS phrasegroup_holiday,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM vb3_user AS user
INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid)
INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
WHERE user.userid = 2
Time before: 0.011343002319336
Time after: 0.013185024261475
Time taken: 0.0018420219421387
table type possible_keys key key_len ref rows Extra
language system PRIMARY 1
user const PRIMARY PRIMARY 4 const 1
userfield const PRIMARY PRIMARY 4 const 1
usertextfield const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
End sessions.php processing
Time after: 0.015431880950928
Time taken: 0.0056309700012207
--------------------------------------------------------------------------------
Query:
SELECT calendarpermission.usergroupid, calendarpermission.calendarpermissions,calendar.ca lendarid,calendar.title, displayorder
FROM vb3_calendar AS calendar
LEFT JOIN vb3_calendarpermission AS calendarpermission ON (calendarpermission.calendarid=calendar.calendarid AND usergroupid IN(15, 6))
ORDER BY displayorder ASC
Time before: 0.018037080764771
Time after: 0.018287897109985
Time taken: 0.00025081634521484
table type possible_keys key key_len ref rows Extra
calendar ALL 2 Using temporary; Using filesort
calendarpermission range calendarid,usergroupid usergroupid 2 2 Using where
--------------------------------------------------------------------------------
Query:
SELECT * FROM vb3_style
WHERE (styleid = 2)
OR styleid = 2
ORDER BY styleid ASC
LIMIT 1
Time before: 0.019032955169678
Time after: 0.019274950027466
Time taken: 0.00024199485778809
table type possible_keys key key_len ref rows Extra
vb3_style system PRIMARY 1
--------------------------------------------------------------------------------
Query:
SELECT title, template
FROM vb3_template
WHERE templateid IN (4534,4463,4208,4505,4537,4211,4538,4459,4214,4215 ,0,4216,4217,4218,4219,4498,4838,4835,4421,4502,42 99,4310,4461,4278,4416,4419,4426,4296,4297,4298,43 87,4431,4430,4437,4436,4435,4509,4434)
Time before: 0.0207679271698
Time after: 0.021266937255859
Time taken: 0.00049901008605957
table type possible_keys key key_len ref rows Extra
vb3_template range PRIMARY PRIMARY 4 38 Using where
--------------------------------------------------------------------------------
End call of global.php: 0.028837919235229
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948'
Time before: 0.028907060623169
Time after: 0.041484117507935
Time taken: 0.012577056884766
table type possible_keys key key_len ref rows Extra
vb3_thread ALL 3057 Using where
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.041922092437744
Time after: 0.042051076889038
Time taken: 0.00012898445129395
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Query:
SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, session.inforum, session.lastactivity,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_session AS session
LEFT JOIN vb3_user AS user ON(user.userid = session.userid)
WHERE session.lastactivity > 1088125372
ORDER BY invisible ASC, username ASC, lastactivity DESC
Time before: 0.045293092727661
Time after: 0.046397924423218
Time taken: 0.0011048316955566
table type possible_keys key key_len ref rows Extra
session ALL 40 Using where; Using temporary; Using filesort
user eq_ref PRIMARY PRIMARY 4 session.userid 1
--------------------------------------------------------------------------------
Query:
SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount
FROM vb3_forum AS forum
Time before: 0.048391103744507
Time after: 0.048739910125732
Time taken: 0.00034880638122559
table type possible_keys key key_len ref rows Extra
forum ALL 45
--------------------------------------------------------------------------------
Query:
SELECT count(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.10724306106567
Time after: 0.1074640750885
Time taken: 0.00022101402282715
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Page generated in 0.11411595 seconds with 14 queries,
spending 0.020371437072754 doing MySQL queries and 0.093744512927246 doing PHP things.
--------------------------------------------------------------------------------Shutdown Queries:--------------------------------------------------------------------------------
Query:
UPDATE vb3_user
SET lastactivity = 1088128972
WHERE userid = 2
Time before: 0.1202449798584
Time after: 0.12073993682861
Time taken: 0.00049495697021484
--------------------------------------------------------------------------------
Query:
UPDATE vb3_session
SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088128972, location = "/index.php?&explain=1", styleid = 0, bypass = 0
,inforum = 0, inthread = 0, incalendar = 0, badlocation = 0
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
Time before: 0.12086510658264
Time after: 0.12108993530273
Time taken: 0.00022482872009277
--------------------------------------------------------------------------------
here is the explanation for whos online.php
Page generated in 0.10043406 seconds (88.86% PHP - 11.14% MySQL) with 20 queries
Connecting to database
Time before: 0.0017991065979004
Time after: 0.0022170543670654
Time taken: 0.00041794776916504
--------------------------------------------------------------------------------
Query:
SELECT title, data FROM vb3_datastore
WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'maxloggedin', 'wol_spiders')
Time before: 0.0023200511932373
Time after: 0.0032210350036621
Time taken: 0.0009009838104248
table type possible_keys key key_len ref rows Extra
vb3_datastore range PRIMARY PRIMARY 15 8 Using where
--------------------------------------------------------------------------------
Parsing functions.php
Time before: 0.0070469379425049
Time after: 0.010152101516724
Time taken: 0.0031051635742188
--------------------------------------------------------------------------------
Processing sessions.php
Time before: 0.010451078414917
Query:
SELECT *
FROM vb3_session
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
AND lastactivity > 1088125564
AND host = '203.115.83.97'
AND idhash = 'b00220156f5ad4123395dfe1da00e9e3'
Time before: 0.010966062545776
Time after: 0.011430025100708
Time taken: 0.00046396255493164
table type possible_keys key key_len ref rows Extra
vb3_session const PRIMARY PRIMARY 32 const 1
--------------------------------------------------------------------------------
Query:
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_wol AS phrasegroup_wol,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM vb3_user AS user
INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid)
INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
WHERE user.userid = 2
Time before: 0.012029886245728
Time after: 0.013887882232666
Time taken: 0.0018579959869385
table type possible_keys key key_len ref rows Extra
language system PRIMARY 1
user const PRIMARY PRIMARY 4 const 1
userfield const PRIMARY PRIMARY 4 const 1
usertextfield const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
End sessions.php processing
Time after: 0.016139030456543
Time taken: 0.005687952041626
--------------------------------------------------------------------------------
Query:
SELECT calendarpermission.usergroupid, calendarpermission.calendarpermissions,calendar.ca lendarid,calendar.title, displayorder
FROM vb3_calendar AS calendar
LEFT JOIN vb3_calendarpermission AS calendarpermission ON (calendarpermission.calendarid=calendar.calendarid AND usergroupid IN(15, 6))
ORDER BY displayorder ASC
Time before: 0.019282102584839
Time after: 0.01952600479126
Time taken: 0.0002439022064209
table type possible_keys key key_len ref rows Extra
calendar ALL 2 Using temporary; Using filesort
calendarpermission range calendarid,usergroupid usergroupid 2 2 Using where
--------------------------------------------------------------------------------
Query:
SELECT * FROM vb3_style
WHERE (styleid = 2)
OR styleid = 2
ORDER BY styleid ASC
LIMIT 1
Time before: 0.020247936248779
Time after: 0.020488023757935
Time taken: 0.00024008750915527
table type possible_keys key key_len ref rows Extra
vb3_style system PRIMARY 1
--------------------------------------------------------------------------------
Query:
SELECT title, template
FROM vb3_template
WHERE templateid IN (4206,0,4230,4231,4233,4238,4540,4512,4838,4835,44 21,4502,4299,4310,4461,4278,4416,4419,4426,4296,42 97,4298,4387,4431,4430,4437,4436,4435,4509,4434)
Time before: 0.02186393737793
Time after: 0.022325038909912
Time taken: 0.00046110153198242
table type possible_keys key key_len ref rows Extra
vb3_template range PRIMARY PRIMARY 4 30 Using where
--------------------------------------------------------------------------------
End call of global.php: 0.029645919799805
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948'
Time before: 0.029716014862061
Time after: 0.029887914657593
Time taken: 0.00017189979553223
table type possible_keys key key_len ref rows Extra
vb3_thread ALL 3057 Using where
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.030333042144775
Time after: 0.030464887619019
Time taken: 0.00013184547424316
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Query:
SELECT user.username, session.useragent, session.location, session.lastactivity, user.userid, user.options, session.host, session.badlocation, session.incalendar, user.aim, user.icq, user.msn, user.yahoo,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_session AS session
LEFT JOIN vb3_user AS user USING (userid)
WHERE session.lastactivity > 1088125564
ORDER BY user.username asc
Time before: 0.032867908477783
Time after: 0.034101009368896
Time taken: 0.0012331008911133
table type possible_keys key key_len ref rows Extra
session ALL 35 Using where; Using temporary; Using filesort
user eq_ref PRIMARY PRIMARY 4 vbdb_f1nalDbaS3.session.userid 1
--------------------------------------------------------------------------------
Query:
SELECT DISTINCT userid FROM vb3_moderator
Time before: 0.034611940383911
Time after: 0.034775018692017
Time taken: 0.00016307830810547
table type possible_keys key key_len ref rows Extra
vb3_moderator index userid 6 76 Using index
--------------------------------------------------------------------------------
Query:
SELECT shortname,title,gamesettings,highscorerid,highscor e,miniimage,gameid FROM vb3_games
Time before: 0.03780198097229
Time after: 0.038661956787109
Time taken: 0.00085997581481934
table type possible_keys key key_len ref rows Extra
vb3_games ALL 180
--------------------------------------------------------------------------------
Query:
SELECT threadid, postid
FROM vb3_post
WHERE postid IN (0,34597,32579,34752,32579)
Time before: 0.069528102874756
Time after: 0.069756031036377
Time taken: 0.00022792816162109
table type possible_keys key key_len ref rows Extra
vb3_post range PRIMARY PRIMARY 4 4 Using where
--------------------------------------------------------------------------------
Query:
SELECT thread.title, thread.threadid, thread.forumid, thread.postuserid, thread.visible,
NOT ISNULL(deletionlog.primaryid) AS isdeleted
, NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
FROM vb3_thread AS thread
LEFT JOIN vb3_deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = 'thread')
LEFT JOIN vb3_subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 2)
WHERE thread.threadid IN (0,2399,2028,2028,2028,3403,0,3044,2337,3044,2586, 2028,2028,2244,3390,3403)
Time before: 0.070334911346436
Time after: 0.07055401802063
Time taken: 0.00021910667419434
table type possible_keys key key_len ref rows Extra
thread range PRIMARY PRIMARY 4 9 Using where
deletionlog eq_ref PRIMARY PRIMARY 5 thread.threadid,const 1 Using index
subscribethread eq_ref subscribeindex,threadid subscribeindex 8 const,thread.threadid 1 Using where
--------------------------------------------------------------------------------
Query:
SELECT pmtext.fromuserid, pm.pmid
FROM vb3_pm AS pm
LEFT JOIN vb3_pmtext AS pmtext ON (pm.pmtextid = pmtext.pmtextid)
WHERE pmid IN (0,4589)
Time before: 0.07145094871521
Time after: 0.071608066558838
Time taken: 0.00015711784362793
table type possible_keys key key_len ref rows Extra
pm range PRIMARY PRIMARY 4 2 Using where
pmtext eq_ref PRIMARY PRIMARY 4 pm.pmtextid 1
--------------------------------------------------------------------------------
Query:
SELECT searchid, search.userid, query, searchuser, user.userid AS targetuserid
FROM vb3_search AS search
LEFT JOIN vb3_user AS user ON (user.username = search.searchuser)
WHERE searchid IN (0,19364,19359,19362)
Time before: 0.072097063064575
Time after: 0.072566986083984
Time taken: 0.00046992301940918
table type possible_keys key key_len ref rows Extra
search range PRIMARY PRIMARY 4 4 Using where
user ref username username 50 search.searchuser 1
--------------------------------------------------------------------------------
Query:
SELECT userid, username, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_user AS user
WHERE userid IN (0,118,378)
Time before: 0.073144912719727
Time after: 0.073514938354492
Time taken: 0.00037002563476562
table type possible_keys key key_len ref rows Extra
user range PRIMARY PRIMARY 4 3 Using where
--------------------------------------------------------------------------------
Query:
SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount
FROM vb3_forum AS forum
Time before: 0.093667030334473
Time after: 0.094094038009644
Time taken: 0.0004270076751709
table type possible_keys key key_len ref rows Extra
forum ALL 45
--------------------------------------------------------------------------------
Page generated in 0.10758400 seconds with 20 queries,
spending 0.01093578338623 doing MySQL queries and 0.09664821661377 doing PHP things.
--------------------------------------------------------------------------------Shutdown Queries:--------------------------------------------------------------------------------
Query:
UPDATE vb3_user
SET lastactivity = 1088129164
WHERE userid = 2
Time before: 0.11364102363586
Time after: 0.11438608169556
Time taken: 0.00074505805969238
--------------------------------------------------------------------------------
Query:
UPDATE vb3_session
SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088129164, location = "/online.php?pp=30&explain=1", styleid = 0, bypass = 0
,inforum = 0, inthread = 0, incalendar = 0, badlocation = 0
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
Time before: 0.11453795433044
Time after: 0.114825963974
Time taken: 0.00028800964355469
--------------------------------------------------------------------------------
showthread.php?t=3390
Page generated in 0.22897100 seconds (67.33% PHP - 32.67% MySQL) with 18 queries
Connecting to database
Time before: 0.0017850399017334
Time after: 0.0022079944610596
Time taken: 0.00042295455932617
--------------------------------------------------------------------------------
Query:
SELECT title, data FROM vb3_datastore
WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'rankphp', 'smiliecache', 'bbcodecache', 'mailqueue', 'hidprofilecache')
Time before: 0.0023159980773926
Time after: 0.0027599334716797
Time taken: 0.00044393539428711
table type possible_keys key key_len ref rows Extra
vb3_datastore range PRIMARY PRIMARY 15 11 Using where
--------------------------------------------------------------------------------
Parsing functions.php
Time before: 0.0077250003814697
Time after: 0.010399103164673
Time taken: 0.0026741027832031
--------------------------------------------------------------------------------
Processing sessions.php
Time before: 0.010664939880371
Query:
SELECT *
FROM vb3_session
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
AND lastactivity > 1088125716
AND host = '203.115.83.97'
AND idhash = 'b00220156f5ad4123395dfe1da00e9e3'
Time before: 0.011117935180664
Time after: 0.01154088973999
Time taken: 0.00042295455932617
table type possible_keys key key_len ref rows Extra
vb3_session const PRIMARY PRIMARY 32 const 1
--------------------------------------------------------------------------------
Query:
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_posting AS phrasegroup_posting,
language.phrasegroup_postbit AS phrasegroup_postbit,
language.phrasegroup_showthread AS phrasegroup_showthread,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM vb3_user AS user
INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid)
INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
WHERE user.userid = 2
Time before: 0.012130975723267
Time after: 0.013999938964844
Time taken: 0.0018689632415771
table type possible_keys key key_len ref rows Extra
language system PRIMARY 1
user const PRIMARY PRIMARY 4 const 1
userfield const PRIMARY PRIMARY 4 const 1
usertextfield const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
End sessions.php processing
Time after: 0.01623010635376
Time taken: 0.0055651664733887
--------------------------------------------------------------------------------
Query:
SELECT NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,
deletionlog.username AS del_username, deletionlog.reason AS del_reason,
NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed, emailupdate, folderid,
thread.*
FROM vb3_thread AS thread
LEFT JOIN vb3_deletionlog AS deletionlog ON (deletionlog.primaryid = thread.threadid AND deletionlog.type = 'thread')
LEFT JOIN vb3_subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = 2)
WHERE thread.threadid = 3372
Time before: 0.01953911781311
Time after: 0.019848108291626
Time taken: 0.00030899047851562
table type possible_keys key key_len ref rows Extra
thread const PRIMARY PRIMARY 4 const 1
deletionlog const PRIMARY PRIMARY 5 const,const 0 unique row not found
subscribethread const subscribeindex,threadid subscribeindex 8 const,const 0 unique row not found
--------------------------------------------------------------------------------
Query:
SELECT * FROM vb3_style
WHERE (styleid = 2)
OR styleid = 2
ORDER BY styleid ASC
LIMIT 1
Time before: 0.020880937576294
Time after: 0.021111011505127
Time taken: 0.00023007392883301
table type possible_keys key key_len ref rows Extra
vb3_style system PRIMARY 1
--------------------------------------------------------------------------------
Query:
SELECT title, template
FROM vb3_template
WHERE templateid IN (4204,4417,4230,4231,4233,4238,4441,4325,4326,4328 ,4327,4329,0,4330,4331,4819,4334,4335,4822,4144,48 23,4146,4467,4356,4359,4360,4357,4318,4317,4319,43 23,4321,4469,4838,4835,4421,4502,4299,4310,4461,42 78,4416,4419,4426,4296,4297,4298,4387,4431,4430,44 37,4436,4435,4509,4434)
Time before: 0.02262806892395
Time after: 0.023199081420898
Time taken: 0.00057101249694824
table type possible_keys key key_len ref rows Extra
vb3_template range PRIMARY PRIMARY 4 55 Using where
--------------------------------------------------------------------------------
End call of global.php: 0.030760049819946
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948'
Time before: 0.030827045440674
Time after: 0.030998945236206
Time taken: 0.00017189979553223
table type possible_keys key key_len ref rows Extra
vb3_thread ALL 3057 Using where
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.031389951705933
Time after: 0.031514883041382
Time taken: 0.00012493133544922
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Query:
SELECT shortname,title,gamesettings,highscorerid,highscor e,miniimage,gameid FROM vb3_games
Time before: 0.034015893936157
Time after: 0.034852027893066
Time taken: 0.00083613395690918
table type possible_keys key key_len ref rows Extra
vb3_games ALL 180
--------------------------------------------------------------------------------
Query:
SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount
FROM vb3_forum AS forum
Time before: 0.043256044387817
Time after: 0.043632030487061
Time taken: 0.00037598609924316
table type possible_keys key key_len ref rows Extra
forum ALL 45
--------------------------------------------------------------------------------
Query:
SELECT user.* FROM vb3_user AS user WHERE userid IN (2,304,118,333,599,85,6,184,572,130,516,164,150,65 ,198,76,159,28,38,465,413,136,5,41,276,89,493,75,1 76,193,95,103) ORDER BY username
Time before: 0.052366018295288
Time after: 0.054105997085571
Time taken: 0.0017399787902832
table type possible_keys key key_len ref rows Extra
user range PRIMARY PRIMARY 4 32 Using where; Using filesort
--------------------------------------------------------------------------------
Query:
SELECT *
FROM vb3_poll
WHERE pollid = 80
Time before: 0.058939933776855
Time after: 0.059132099151611
Time taken: 0.00019216537475586
table type possible_keys key key_len ref rows Extra
vb3_poll const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
Query:
SELECT voteoption
FROM vb3_pollvote
WHERE userid = 2 AND pollid = 80
Time before: 0.066688060760498
Time after: 0.066867113113403
Time taken: 0.00017905235290527
table type possible_keys key key_len ref rows Extra
vb3_pollvote ref userid userid 8 const,const 1 Using where
--------------------------------------------------------------------------------
Query:
SELECT postid, NOT ISNULL(deletionlog.primaryid) AS isdeleted
FROM vb3_post AS post
LEFT JOIN vb3_deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND type = 'post')
WHERE threadid = 3372 AND visible = 1
ORDER BY dateline
Time before: 0.075968980789185
Time after: 0.076198101043701
Time taken: 0.0002291202545166
table type possible_keys key key_len ref rows Extra
post ref threadid threadid 4 const 17 Using where; Using filesort
deletionlog eq_ref PRIMARY PRIMARY 5 post.postid,const 1 Using index
--------------------------------------------------------------------------------
Query:
SELECT
post.*, post.username AS postusername, post.ipaddress AS ip,
user.*, userfield.*, usertextfield.*,
icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.avatardata) AS hascustomavatar, customavatar.dateline AS avatardateline,
level,
NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason,
post_parsed.pagetext_html, post_parsed.hasimages,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_post AS post
LEFT JOIN vb3_user AS user ON(user.userid = post.userid)
LEFT JOIN vb3_userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN vb3_usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN vb3_icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN vb3_avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN vb3_customavatar AS customavatar ON(customavatar.userid = user.userid) LEFT JOIN vb3_reputationlevel AS reputationlevel ON(user.reputationlevelid = reputationlevel.reputationlevelid)
LEFT JOIN vb3_deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type = 'post')
LEFT JOIN vb3_editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN vb3_post_parsed AS post_parsed ON(post_parsed.postid = post.postid)
WHERE post.postid IN (0,34383,34392,34413,34447,34482,34483,34487,34489 ,34491,34493,34548,34555,34577,34578)
ORDER BY dateline
Time before: 0.077088117599487
Time after: 0.080060958862305
Time taken: 0.0029728412628174
table type possible_keys key key_len ref rows Extra
post range PRIMARY PRIMARY 4 15 Using where; Using filesort
user eq_ref PRIMARY PRIMARY 4 post.userid 1
userfield eq_ref PRIMARY PRIMARY 4 user.userid 1
usertextfield eq_ref PRIMARY PRIMARY 4 user.userid 1
icon eq_ref PRIMARY PRIMARY 2 post.iconid 1
avatar eq_ref PRIMARY PRIMARY 2 user.avatarid 1
customavatar eq_ref PRIMARY PRIMARY 4 user.userid 1
reputationlevel eq_ref PRIMARY PRIMARY 4 user.reputationlevelid 1
deletionlog eq_ref PRIMARY PRIMARY 5 post.postid,const 1
editlog eq_ref PRIMARY PRIMARY 4 post.postid 1
post_parsed eq_ref PRIMARY PRIMARY 4 post.postid 1
--------------------------------------------------------------------------------
Time after parsing all posts: 0.15415096282959
--------------------------------------------------------------------------------
Page generated in 0.16847706 seconds with 18 queries,
spending 0.012998104095459 doing MySQL queries and 0.15547895590454 doing PHP things.
--------------------------------------------------------------------------------Shutdown Queries:--------------------------------------------------------------------------------
Query:
UPDATE vb3_user
SET lastactivity = 1088129316
WHERE userid = 2
Time before: 0.17586708068848
Time after: 0.17638993263245
Time taken: 0.00052285194396973
--------------------------------------------------------------------------------
Query:
UPDATE vb3_session
SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088129316, location = "/showthread.php?t=3372&explain=1", styleid = 0, bypass = 0
,inforum = 45, inthread = 3372, incalendar = 0, badlocation = 0
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
Time before: 0.17650890350342
Time after: 0.1767430305481
Time taken: 0.00023412704467773
--------------------------------------------------------------------------------
Query:
INSERT INTO vb3_threadviews (threadid,+++++adid,+++++adtime)
VALUES (3372, 2, '1088129316')
Time before: 0.17688894271851
Time after: 0.17707800865173
Time taken: 0.00018906593322754
--------------------------------------------------------------------------------
Zachery
06-25-2004, 01:51 AM
i enabled the debug mode in config.php and now check my index.php load
Page generated in 0.19459200 seconds (46.26% PHP - 53.74% MySQL) with 20 queries wierd
here is the explanation of index.php
Connecting to database
Time before: 0.001802921295166
Time after: 0.0022330284118652
Time taken: 0.00043010711669922
--------------------------------------------------------------------------------
Query:
SELECT title, data FROM vb3_datastore
WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'memberoftheday', 'userstats', 'birthdaycache', 'maxloggedin', 'iconcache', 'eventcache', 'mailqueue')
Time before: 0.0023479461669922
Time after: 0.0027229785919189
Time taken: 0.00037503242492676
table type possible_keys key key_len ref rows Extra
vb3_datastore range PRIMARY PRIMARY 15 13 Using where
--------------------------------------------------------------------------------
Parsing functions.php
Time before: 0.006195068359375
Time after: 0.0094931125640869
Time taken: 0.0032980442047119
--------------------------------------------------------------------------------
Processing sessions.php
Time before: 0.009800910949707
Query:
SELECT *
FROM vb3_session
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
AND lastactivity > 1088125372
AND host = '203.115.83.97'
AND idhash = 'b00220156f5ad4123395dfe1da00e9e3'
Time before: 0.010317087173462
Time after: 0.010741949081421
Time taken: 0.00042486190795898
table type possible_keys key key_len ref rows Extra
vb3_session const PRIMARY PRIMARY 32 const 1
--------------------------------------------------------------------------------
Query:
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_holiday AS phrasegroup_holiday,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM vb3_user AS user
INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid)
INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
WHERE user.userid = 2
Time before: 0.011343002319336
Time after: 0.013185024261475
Time taken: 0.0018420219421387
table type possible_keys key key_len ref rows Extra
language system PRIMARY 1
user const PRIMARY PRIMARY 4 const 1
userfield const PRIMARY PRIMARY 4 const 1
usertextfield const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
End sessions.php processing
Time after: 0.015431880950928
Time taken: 0.0056309700012207
--------------------------------------------------------------------------------
Query:
SELECT calendarpermission.usergroupid, calendarpermission.calendarpermissions,calendar.ca lendarid,calendar.title, displayorder
FROM vb3_calendar AS calendar
LEFT JOIN vb3_calendarpermission AS calendarpermission ON (calendarpermission.calendarid=calendar.calendarid AND usergroupid IN(15, 6))
ORDER BY displayorder ASC
Time before: 0.018037080764771
Time after: 0.018287897109985
Time taken: 0.00025081634521484
table type possible_keys key key_len ref rows Extra
calendar ALL 2 Using temporary; Using filesort
calendarpermission range calendarid,usergroupid usergroupid 2 2 Using where
--------------------------------------------------------------------------------
Query:
SELECT * FROM vb3_style
WHERE (styleid = 2)
OR styleid = 2
ORDER BY styleid ASC
LIMIT 1
Time before: 0.019032955169678
Time after: 0.019274950027466
Time taken: 0.00024199485778809
table type possible_keys key key_len ref rows Extra
vb3_style system PRIMARY 1
--------------------------------------------------------------------------------
Query:
SELECT title, template
FROM vb3_template
WHERE templateid IN (4534,4463,4208,4505,4537,4211,4538,4459,4214,4215 ,0,4216,4217,4218,4219,4498,4838,4835,4421,4502,42 99,4310,4461,4278,4416,4419,4426,4296,4297,4298,43 87,4431,4430,4437,4436,4435,4509,4434)
Time before: 0.0207679271698
Time after: 0.021266937255859
Time taken: 0.00049901008605957
table type possible_keys key key_len ref rows Extra
vb3_template range PRIMARY PRIMARY 4 38 Using where
--------------------------------------------------------------------------------
End call of global.php: 0.028837919235229
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948'
Time before: 0.028907060623169
Time after: 0.041484117507935
Time taken: 0.012577056884766
table type possible_keys key key_len ref rows Extra
vb3_thread ALL 3057 Using where
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.041922092437744
Time after: 0.042051076889038
Time taken: 0.00012898445129395
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Query:
SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, session.inforum, session.lastactivity,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_session AS session
LEFT JOIN vb3_user AS user ON(user.userid = session.userid)
WHERE session.lastactivity > 1088125372
ORDER BY invisible ASC, username ASC, lastactivity DESC
Time before: 0.045293092727661
Time after: 0.046397924423218
Time taken: 0.0011048316955566
table type possible_keys key key_len ref rows Extra
session ALL 40 Using where; Using temporary; Using filesort
user eq_ref PRIMARY PRIMARY 4 session.userid 1
--------------------------------------------------------------------------------
Query:
SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount
FROM vb3_forum AS forum
Time before: 0.048391103744507
Time after: 0.048739910125732
Time taken: 0.00034880638122559
table type possible_keys key key_len ref rows Extra
forum ALL 45
--------------------------------------------------------------------------------
Query:
SELECT count(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.10724306106567
Time after: 0.1074640750885
Time taken: 0.00022101402282715
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Page generated in 0.11411595 seconds with 14 queries,
spending 0.020371437072754 doing MySQL queries and 0.093744512927246 doing PHP things.
--------------------------------------------------------------------------------Shutdown Queries:--------------------------------------------------------------------------------
Query:
UPDATE vb3_user
SET lastactivity = 1088128972
WHERE userid = 2
Time before: 0.1202449798584
Time after: 0.12073993682861
Time taken: 0.00049495697021484
--------------------------------------------------------------------------------
Query:
UPDATE vb3_session
SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088128972, location = "/index.php?&explain=1", styleid = 0, bypass = 0
,inforum = 0, inthread = 0, incalendar = 0, badlocation = 0
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
Time before: 0.12086510658264
Time after: 0.12108993530273
Time taken: 0.00022482872009277
--------------------------------------------------------------------------------
here is the explanation for whos online.php
Page generated in 0.10043406 seconds (88.86% PHP - 11.14% MySQL) with 20 queries
Connecting to database
Time before: 0.0017991065979004
Time after: 0.0022170543670654
Time taken: 0.00041794776916504
--------------------------------------------------------------------------------
Query:
SELECT title, data FROM vb3_datastore
WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'maxloggedin', 'wol_spiders')
Time before: 0.0023200511932373
Time after: 0.0032210350036621
Time taken: 0.0009009838104248
table type possible_keys key key_len ref rows Extra
vb3_datastore range PRIMARY PRIMARY 15 8 Using where
--------------------------------------------------------------------------------
Parsing functions.php
Time before: 0.0070469379425049
Time after: 0.010152101516724
Time taken: 0.0031051635742188
--------------------------------------------------------------------------------
Processing sessions.php
Time before: 0.010451078414917
Query:
SELECT *
FROM vb3_session
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
AND lastactivity > 1088125564
AND host = '203.115.83.97'
AND idhash = 'b00220156f5ad4123395dfe1da00e9e3'
Time before: 0.010966062545776
Time after: 0.011430025100708
Time taken: 0.00046396255493164
table type possible_keys key key_len ref rows Extra
vb3_session const PRIMARY PRIMARY 32 const 1
--------------------------------------------------------------------------------
Query:
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_wol AS phrasegroup_wol,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM vb3_user AS user
INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid)
INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
WHERE user.userid = 2
Time before: 0.012029886245728
Time after: 0.013887882232666
Time taken: 0.0018579959869385
table type possible_keys key key_len ref rows Extra
language system PRIMARY 1
user const PRIMARY PRIMARY 4 const 1
userfield const PRIMARY PRIMARY 4 const 1
usertextfield const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
End sessions.php processing
Time after: 0.016139030456543
Time taken: 0.005687952041626
--------------------------------------------------------------------------------
Query:
SELECT calendarpermission.usergroupid, calendarpermission.calendarpermissions,calendar.ca lendarid,calendar.title, displayorder
FROM vb3_calendar AS calendar
LEFT JOIN vb3_calendarpermission AS calendarpermission ON (calendarpermission.calendarid=calendar.calendarid AND usergroupid IN(15, 6))
ORDER BY displayorder ASC
Time before: 0.019282102584839
Time after: 0.01952600479126
Time taken: 0.0002439022064209
table type possible_keys key key_len ref rows Extra
calendar ALL 2 Using temporary; Using filesort
calendarpermission range calendarid,usergroupid usergroupid 2 2 Using where
--------------------------------------------------------------------------------
Query:
SELECT * FROM vb3_style
WHERE (styleid = 2)
OR styleid = 2
ORDER BY styleid ASC
LIMIT 1
Time before: 0.020247936248779
Time after: 0.020488023757935
Time taken: 0.00024008750915527
table type possible_keys key key_len ref rows Extra
vb3_style system PRIMARY 1
--------------------------------------------------------------------------------
Query:
SELECT title, template
FROM vb3_template
WHERE templateid IN (4206,0,4230,4231,4233,4238,4540,4512,4838,4835,44 21,4502,4299,4310,4461,4278,4416,4419,4426,4296,42 97,4298,4387,4431,4430,4437,4436,4435,4509,4434)
Time before: 0.02186393737793
Time after: 0.022325038909912
Time taken: 0.00046110153198242
table type possible_keys key key_len ref rows Extra
vb3_template range PRIMARY PRIMARY 4 30 Using where
--------------------------------------------------------------------------------
End call of global.php: 0.029645919799805
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948'
Time before: 0.029716014862061
Time after: 0.029887914657593
Time taken: 0.00017189979553223
table type possible_keys key key_len ref rows Extra
vb3_thread ALL 3057 Using where
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.030333042144775
Time after: 0.030464887619019
Time taken: 0.00013184547424316
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Query:
SELECT user.username, session.useragent, session.location, session.lastactivity, user.userid, user.options, session.host, session.badlocation, session.incalendar, user.aim, user.icq, user.msn, user.yahoo,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_session AS session
LEFT JOIN vb3_user AS user USING (userid)
WHERE session.lastactivity > 1088125564
ORDER BY user.username asc
Time before: 0.032867908477783
Time after: 0.034101009368896
Time taken: 0.0012331008911133
table type possible_keys key key_len ref rows Extra
session ALL 35 Using where; Using temporary; Using filesort
user eq_ref PRIMARY PRIMARY 4 vbdb_f1nalDbaS3.session.userid 1
--------------------------------------------------------------------------------
Query:
SELECT DISTINCT userid FROM vb3_moderator
Time before: 0.034611940383911
Time after: 0.034775018692017
Time taken: 0.00016307830810547
table type possible_keys key key_len ref rows Extra
vb3_moderator index userid 6 76 Using index
--------------------------------------------------------------------------------
Query:
SELECT shortname,title,gamesettings,highscorerid,highscor e,miniimage,gameid FROM vb3_games
Time before: 0.03780198097229
Time after: 0.038661956787109
Time taken: 0.00085997581481934
table type possible_keys key key_len ref rows Extra
vb3_games ALL 180
--------------------------------------------------------------------------------
Query:
SELECT threadid, postid
FROM vb3_post
WHERE postid IN (0,34597,32579,34752,32579)
Time before: 0.069528102874756
Time after: 0.069756031036377
Time taken: 0.00022792816162109
table type possible_keys key key_len ref rows Extra
vb3_post range PRIMARY PRIMARY 4 4 Using where
--------------------------------------------------------------------------------
Query:
SELECT thread.title, thread.threadid, thread.forumid, thread.postuserid, thread.visible,
NOT ISNULL(deletionlog.primaryid) AS isdeleted
, NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
FROM vb3_thread AS thread
LEFT JOIN vb3_deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = 'thread')
LEFT JOIN vb3_subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 2)
WHERE thread.threadid IN (0,2399,2028,2028,2028,3403,0,3044,2337,3044,2586, 2028,2028,2244,3390,3403)
Time before: 0.070334911346436
Time after: 0.07055401802063
Time taken: 0.00021910667419434
table type possible_keys key key_len ref rows Extra
thread range PRIMARY PRIMARY 4 9 Using where
deletionlog eq_ref PRIMARY PRIMARY 5 thread.threadid,const 1 Using index
subscribethread eq_ref subscribeindex,threadid subscribeindex 8 const,thread.threadid 1 Using where
--------------------------------------------------------------------------------
Query:
SELECT pmtext.fromuserid, pm.pmid
FROM vb3_pm AS pm
LEFT JOIN vb3_pmtext AS pmtext ON (pm.pmtextid = pmtext.pmtextid)
WHERE pmid IN (0,4589)
Time before: 0.07145094871521
Time after: 0.071608066558838
Time taken: 0.00015711784362793
table type possible_keys key key_len ref rows Extra
pm range PRIMARY PRIMARY 4 2 Using where
pmtext eq_ref PRIMARY PRIMARY 4 pm.pmtextid 1
--------------------------------------------------------------------------------
Query:
SELECT searchid, search.userid, query, searchuser, user.userid AS targetuserid
FROM vb3_search AS search
LEFT JOIN vb3_user AS user ON (user.username = search.searchuser)
WHERE searchid IN (0,19364,19359,19362)
Time before: 0.072097063064575
Time after: 0.072566986083984
Time taken: 0.00046992301940918
table type possible_keys key key_len ref rows Extra
search range PRIMARY PRIMARY 4 4 Using where
user ref username username 50 search.searchuser 1
--------------------------------------------------------------------------------
Query:
SELECT userid, username, IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_user AS user
WHERE userid IN (0,118,378)
Time before: 0.073144912719727
Time after: 0.073514938354492
Time taken: 0.00037002563476562
table type possible_keys key key_len ref rows Extra
user range PRIMARY PRIMARY 4 3 Using where
--------------------------------------------------------------------------------
Query:
SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount
FROM vb3_forum AS forum
Time before: 0.093667030334473
Time after: 0.094094038009644
Time taken: 0.0004270076751709
table type possible_keys key key_len ref rows Extra
forum ALL 45
--------------------------------------------------------------------------------
Page generated in 0.10758400 seconds with 20 queries,
spending 0.01093578338623 doing MySQL queries and 0.09664821661377 doing PHP things.
--------------------------------------------------------------------------------Shutdown Queries:--------------------------------------------------------------------------------
Query:
UPDATE vb3_user
SET lastactivity = 1088129164
WHERE userid = 2
Time before: 0.11364102363586
Time after: 0.11438608169556
Time taken: 0.00074505805969238
--------------------------------------------------------------------------------
Query:
UPDATE vb3_session
SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088129164, location = "/online.php?pp=30&explain=1", styleid = 0, bypass = 0
,inforum = 0, inthread = 0, incalendar = 0, badlocation = 0
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
Time before: 0.11453795433044
Time after: 0.114825963974
Time taken: 0.00028800964355469
--------------------------------------------------------------------------------
showthread.php?t=3390
Page generated in 0.22897100 seconds (67.33% PHP - 32.67% MySQL) with 18 queries
Connecting to database
Time before: 0.0017850399017334
Time after: 0.0022079944610596
Time taken: 0.00042295455932617
--------------------------------------------------------------------------------
Query:
SELECT title, data FROM vb3_datastore
WHERE title IN ('options', 'cron', 'forumcache', 'usergroupcache', 'stylecache', 'arcadesettings', 'rankphp', 'smiliecache', 'bbcodecache', 'mailqueue', 'hidprofilecache')
Time before: 0.0023159980773926
Time after: 0.0027599334716797
Time taken: 0.00044393539428711
table type possible_keys key key_len ref rows Extra
vb3_datastore range PRIMARY PRIMARY 15 11 Using where
--------------------------------------------------------------------------------
Parsing functions.php
Time before: 0.0077250003814697
Time after: 0.010399103164673
Time taken: 0.0026741027832031
--------------------------------------------------------------------------------
Processing sessions.php
Time before: 0.010664939880371
Query:
SELECT *
FROM vb3_session
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
AND lastactivity > 1088125716
AND host = '203.115.83.97'
AND idhash = 'b00220156f5ad4123395dfe1da00e9e3'
Time before: 0.011117935180664
Time after: 0.01154088973999
Time taken: 0.00042295455932617
table type possible_keys key key_len ref rows Extra
vb3_session const PRIMARY PRIMARY 32 const 1
--------------------------------------------------------------------------------
Query:
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_posting AS phrasegroup_posting,
language.phrasegroup_postbit AS phrasegroup_postbit,
language.phrasegroup_showthread AS phrasegroup_showthread,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM vb3_user AS user
INNER JOIN vb3_userfield AS userfield ON (user.userid = userfield.userid)
INNER JOIN vb3_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN vb3_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
WHERE user.userid = 2
Time before: 0.012130975723267
Time after: 0.013999938964844
Time taken: 0.0018689632415771
table type possible_keys key key_len ref rows Extra
language system PRIMARY 1
user const PRIMARY PRIMARY 4 const 1
userfield const PRIMARY PRIMARY 4 const 1
usertextfield const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
End sessions.php processing
Time after: 0.01623010635376
Time taken: 0.0055651664733887
--------------------------------------------------------------------------------
Query:
SELECT NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,
deletionlog.username AS del_username, deletionlog.reason AS del_reason,
NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed, emailupdate, folderid,
thread.*
FROM vb3_thread AS thread
LEFT JOIN vb3_deletionlog AS deletionlog ON (deletionlog.primaryid = thread.threadid AND deletionlog.type = 'thread')
LEFT JOIN vb3_subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = 2)
WHERE thread.threadid = 3372
Time before: 0.01953911781311
Time after: 0.019848108291626
Time taken: 0.00030899047851562
table type possible_keys key key_len ref rows Extra
thread const PRIMARY PRIMARY 4 const 1
deletionlog const PRIMARY PRIMARY 5 const,const 0 unique row not found
subscribethread const subscribeindex,threadid subscribeindex 8 const,const 0 unique row not found
--------------------------------------------------------------------------------
Query:
SELECT * FROM vb3_style
WHERE (styleid = 2)
OR styleid = 2
ORDER BY styleid ASC
LIMIT 1
Time before: 0.020880937576294
Time after: 0.021111011505127
Time taken: 0.00023007392883301
table type possible_keys key key_len ref rows Extra
vb3_style system PRIMARY 1
--------------------------------------------------------------------------------
Query:
SELECT title, template
FROM vb3_template
WHERE templateid IN (4204,4417,4230,4231,4233,4238,4441,4325,4326,4328 ,4327,4329,0,4330,4331,4819,4334,4335,4822,4144,48 23,4146,4467,4356,4359,4360,4357,4318,4317,4319,43 23,4321,4469,4838,4835,4421,4502,4299,4310,4461,42 78,4416,4419,4426,4296,4297,4298,4387,4431,4430,44 37,4436,4435,4509,4434)
Time before: 0.02262806892395
Time after: 0.023199081420898
Time taken: 0.00057101249694824
table type possible_keys key key_len ref rows Extra
vb3_template range PRIMARY PRIMARY 4 55 Using where
--------------------------------------------------------------------------------
End call of global.php: 0.030760049819946
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS threads FROM vb3_thread WHERE dateline > '1088125948'
Time before: 0.030827045440674
Time after: 0.030998945236206
Time taken: 0.00017189979553223
table type possible_keys key key_len ref rows Extra
vb3_thread ALL 3057 Using where
--------------------------------------------------------------------------------
Query:
SELECT COUNT(*) AS posts FROM vb3_post WHERE dateline > '1088125948'
Time before: 0.031389951705933
Time after: 0.031514883041382
Time taken: 0.00012493133544922
table type possible_keys key key_len ref rows Extra
vb3_post ALL 33119 Using where
--------------------------------------------------------------------------------
Query:
SELECT shortname,title,gamesettings,highscorerid,highscor e,miniimage,gameid FROM vb3_games
Time before: 0.034015893936157
Time after: 0.034852027893066
Time taken: 0.00083613395690918
table type possible_keys key key_len ref rows Extra
vb3_games ALL 180
--------------------------------------------------------------------------------
Query:
SELECT forum.forumid, lastpost, lastposter, lastthread, lastthreadid, lasticonid, threadcount, replycount
FROM vb3_forum AS forum
Time before: 0.043256044387817
Time after: 0.043632030487061
Time taken: 0.00037598609924316
table type possible_keys key key_len ref rows Extra
forum ALL 45
--------------------------------------------------------------------------------
Query:
SELECT user.* FROM vb3_user AS user WHERE userid IN (2,304,118,333,599,85,6,184,572,130,516,164,150,65 ,198,76,159,28,38,465,413,136,5,41,276,89,493,75,1 76,193,95,103) ORDER BY username
Time before: 0.052366018295288
Time after: 0.054105997085571
Time taken: 0.0017399787902832
table type possible_keys key key_len ref rows Extra
user range PRIMARY PRIMARY 4 32 Using where; Using filesort
--------------------------------------------------------------------------------
Query:
SELECT *
FROM vb3_poll
WHERE pollid = 80
Time before: 0.058939933776855
Time after: 0.059132099151611
Time taken: 0.00019216537475586
table type possible_keys key key_len ref rows Extra
vb3_poll const PRIMARY PRIMARY 4 const 1
--------------------------------------------------------------------------------
Query:
SELECT voteoption
FROM vb3_pollvote
WHERE userid = 2 AND pollid = 80
Time before: 0.066688060760498
Time after: 0.066867113113403
Time taken: 0.00017905235290527
table type possible_keys key key_len ref rows Extra
vb3_pollvote ref userid userid 8 const,const 1 Using where
--------------------------------------------------------------------------------
Query:
SELECT postid, NOT ISNULL(deletionlog.primaryid) AS isdeleted
FROM vb3_post AS post
LEFT JOIN vb3_deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND type = 'post')
WHERE threadid = 3372 AND visible = 1
ORDER BY dateline
Time before: 0.075968980789185
Time after: 0.076198101043701
Time taken: 0.0002291202545166
table type possible_keys key key_len ref rows Extra
post ref threadid threadid 4 const 17 Using where; Using filesort
deletionlog eq_ref PRIMARY PRIMARY 5 post.postid,const 1 Using index
--------------------------------------------------------------------------------
Query:
SELECT
post.*, post.username AS postusername, post.ipaddress AS ip,
user.*, userfield.*, usertextfield.*,
icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.avatardata) AS hascustomavatar, customavatar.dateline AS avatardateline,
level,
NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason,
post_parsed.pagetext_html, post_parsed.hasimages,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM vb3_post AS post
LEFT JOIN vb3_user AS user ON(user.userid = post.userid)
LEFT JOIN vb3_userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN vb3_usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN vb3_icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN vb3_avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN vb3_customavatar AS customavatar ON(customavatar.userid = user.userid) LEFT JOIN vb3_reputationlevel AS reputationlevel ON(user.reputationlevelid = reputationlevel.reputationlevelid)
LEFT JOIN vb3_deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type = 'post')
LEFT JOIN vb3_editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN vb3_post_parsed AS post_parsed ON(post_parsed.postid = post.postid)
WHERE post.postid IN (0,34383,34392,34413,34447,34482,34483,34487,34489 ,34491,34493,34548,34555,34577,34578)
ORDER BY dateline
Time before: 0.077088117599487
Time after: 0.080060958862305
Time taken: 0.0029728412628174
table type possible_keys key key_len ref rows Extra
post range PRIMARY PRIMARY 4 15 Using where; Using filesort
user eq_ref PRIMARY PRIMARY 4 post.userid 1
userfield eq_ref PRIMARY PRIMARY 4 user.userid 1
usertextfield eq_ref PRIMARY PRIMARY 4 user.userid 1
icon eq_ref PRIMARY PRIMARY 2 post.iconid 1
avatar eq_ref PRIMARY PRIMARY 2 user.avatarid 1
customavatar eq_ref PRIMARY PRIMARY 4 user.userid 1
reputationlevel eq_ref PRIMARY PRIMARY 4 user.reputationlevelid 1
deletionlog eq_ref PRIMARY PRIMARY 5 post.postid,const 1
editlog eq_ref PRIMARY PRIMARY 4 post.postid 1
post_parsed eq_ref PRIMARY PRIMARY 4 post.postid 1
--------------------------------------------------------------------------------
Time after parsing all posts: 0.15415096282959
--------------------------------------------------------------------------------
Page generated in 0.16847706 seconds with 18 queries,
spending 0.012998104095459 doing MySQL queries and 0.15547895590454 doing PHP things.
--------------------------------------------------------------------------------Shutdown Queries:--------------------------------------------------------------------------------
Query:
UPDATE vb3_user
SET lastactivity = 1088129316
WHERE userid = 2
Time before: 0.17586708068848
Time after: 0.17638993263245
Time taken: 0.00052285194396973
--------------------------------------------------------------------------------
Query:
UPDATE vb3_session
SET useragent = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', lastactivity = 1088129316, location = "/showthread.php?t=3372&explain=1", styleid = 0, bypass = 0
,inforum = 45, inthread = 3372, incalendar = 0, badlocation = 0
WHERE sessionhash = '9ea9cfb52c9edcae0506778e3a661173'
Time before: 0.17650890350342
Time after: 0.1767430305481
Time taken: 0.00023412704467773
--------------------------------------------------------------------------------
Query:
INSERT INTO vb3_threadviews (threadid,+++++adid,+++++adtime)
VALUES (3372, 2, '1088129316')
Time before: 0.17688894271851
Time after: 0.17707800865173
Time taken: 0.00018906593322754
--------------------------------------------------------------------------------
Like Boofo said, what hacks have you added to index.php
Dean C
06-25-2004, 01:07 PM
index.php != forumhome necessarily Zach ;) Also the functions used on forumhome won't be in the forums root file either :) What we need to know is what mods have you added which affect/change the functionality of how your forums index page work :)
SaN-DeeP
06-26-2004, 11:54 AM
index.php != forumhome necessarily Zach ;) Also the functions used on forumhome won't be in the forums root file either :) What we need to know is what mods have you added which affect/change the functionality of how your forums index page work :)hi dean.
i had tried couple of hacks in recent time.
here is my list of hacks again :)
i am going for a long break, and uninstalling the stuff, which mite create problems for rest of people to manage it :)
vb3 Statistic Version 1.01 (https://vborg.vbsupport.ru/showthread.php?t=65924)
v3 Arcade - Games Arcade & Challenge System [vB 3.0.1] (https://vborg.vbsupport.ru/showthread.php?t=60377)
Refer Friend (https://vborg.vbsupport.ru/showthread.php?t=59708)
How many threads and posts since your last visit! (https://vborg.vbsupport.ru/showthread.php?t=59583)
Debug-mode in Admin-CP and for super admins only (https://vborg.vbsupport.ru/showthread.php?t=61242)
New User Welcome PM [v2] (https://vborg.vbsupport.ru/showthread.php?t=63269)
[HTL] Hack Tracking Log (https://vborg.vbsupport.ru/showthread.php?t=60735) -> will uninstall this not of any use for me
[vbcron] Automatic database backup [v1.3] (https://vborg.vbsupport.ru/showthread.php?t=62149) -> (still not working)
Profile-Views (https://vborg.vbsupport.ru/showthread.php?t=66203)
[VB3 RC3] Who viewed this thread? (https://vborg.vbsupport.ru/showthread.php?t=61225) -> (this mite be cozing problems)
Download Forum Thread To Your Computer (Vb3.x) (https://vborg.vbsupport.ru/showthread.php?t=65536)
Who have you given Reputation to? (https://vborg.vbsupport.ru/showthread.php?t=61518) .
BB|Messenger v1.2 vb2 & v1.3 vb3 (https://vborg.vbsupport.ru/showthread.php?t=56836)
VB3 MicroStats (https://vborg.vbsupport.ru/showthread.php?t=62173) -> (a few queries mite be added of this too)
vbPopup for vB3 (https://vborg.vbsupport.ru/showthread.php?t=61647)
[VB3 RC3] Reputation in member's info (https://vborg.vbsupport.ru/showthread.php?t=60389)
Delete a post with only one button (in postbit) (https://vborg.vbsupport.ru/showthread.php?t=60704) -> (a few queries mite be added of this too)
[VB3 RC3] Member of the day (https://vborg.vbsupport.ru/showthread.php?t=60368) -> (a few queries mite be added of this too)
Advanced Editing Options For Mods & Admins (https://vborg.vbsupport.ru/showthread.php?t=61412)
those are the hacks, installed and adding the load to server. :S
SaN-DeeP
06-26-2004, 06:30 PM
may i bump this again ?
can some sql gurus help me ease my load on forums.
Thnx.
Sandy...
Zachery
06-26-2004, 07:54 PM
How many threads and posts since your last visit!
BB|Messenger v1.2 vb2 & v1.3 vb3
[VB3 RC3] Member of the day -> (a few queries mite be added of this too)
These are the ones that are probaly cuasing the biggest problems
Dean C
06-26-2004, 08:33 PM
The new/threads post mod is notorius for adding server load as in essence. I don't know if its in the installation for the vB3 version of this mod but on the previous one it was reccomended to add an index on the postid column somewhere. Can't remember where though sorry :)
Zachery
06-26-2004, 08:48 PM
The new/threads post mod is notorius for adding server load as in essence. I don't know if its in the installation for the vB3 version of this mod but on the previous one it was reccomended to add an index on the postid column somewhere. Can't remember where though sorry :)
Its still recomended, also, it should only be done once, not every page load (i know a few people who have it on every page)
and BB messager imporply setup can be very server intensive.,
VB3 MicroStats -> (a few queries mite be added of this too)
The hack does not add any queries.
vBulletin® v3.8.12 by vBS, Copyright ©2000-2025, vBulletin Solutions Inc.