Debug Modes & Queries

[Aaron1]

Hi, i was checking my slow loading 2.2.7 frontpage with debug modus on, and I noticed some of the queries takes a long time to load. I copied my showqueries option results here. Maybe you have any idea what is causing the delay?



Query: SELECT template FROM template WHERE title='options'
Time before: 0.18156504631042
Time after: 0.18442296981812

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

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

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

Query: INSERT INTO session (sessionhash,userid,host,useragent,lastactivity,al thash,location) VALUES ('d8708b9c36882818d86d05d69505c7c0','1','194.109.2 27.3','Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)','1077206999','','//forum/index.php?s=&showqueries=1')
Time before: 0.26929306983948
Time after: 0.27953791618347

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


Warning: Cannot add header information - headers already sent by (output started at /home/sites/site5/web/forum/global.php:145) in /home/sites/site5/web/forum/admin/functions.php on line 1700
Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='6'
Time before: 0.28320288658142
Time after: 0.28443598747253

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

Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='1' or styleid=1 ORDER BY styleid DESC
Time before: 0.28557991981506
Time after: 0.2870569229126

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

Query: SELECT template,title
FROM template
WHERE (title IN ('error_nopermission','forumhome_pmloggedin','foru mhome_welcometext',
'forumhome_logoutcode','forumhome_newposts','forum home_logincode',
'forumhome_loggedinuser','forumhome_loggedinusers' ,'forumhome_lastpostby',
'forumhome_moderator','forumhome_forumbit_level1_n opost',
'forumhome_forumbit_level1_post','forumhome_forumb it_level2_nopost',
'forumhome_forumbit_level2_post','forumhome','foru mhome_unregmessage',
'whos_chatting','gobutton','timezone','username_lo ggedout','username_loggedin',
'phpinclude','headinclude','header','footer','foru mjumpbit','forumjump','nav_linkoff',
'nav_linkon','navbar','nav_joiner','pagenav','page nav_curpage','pagenav_firstlink',
'pagenav_lastlink','pagenav_nextlink','pagenav_pag elink','pagenav_prevlink',
'home_microstats')
AND (templatesetid=-1 OR templatesetid='3'))
OR (title = 'maxloggedin')

ORDER BY templatesetid
Time before: 0.28862404823303
Time after: 0.49036908149719

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

Query: SELECT * FROM usergroup WHERE usergroupid=6
Time before: 0.50419306755066
Time after: 0.50593304634094

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

Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND

messageread=0 AND folderid=0
Time before: 0.50736403465271
Time after: 0.51272797584534

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

Query: SELECT smilietext,smiliepath FROM smilie ORDER BY showid
Time before: 0.58423209190369
Time after: 1.3096740245819

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

Query: SELECT bbcodetag,bbcodereplacement,twoparams FROM bbcode
Time before: 1.3222870826721
Time after: 1.3240048885345

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

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

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

Query: SELECT COUNT(*) AS messages, IF(messageread>0, 0, SUM(IF(dateline>1077204189, 1, 0))) as newpm, messageread
FROM privatemessage
WHERE userid=1 AND (dateline>1077204189 OR messageread=0) AND folderid=0
GROUP BY messageread
ORDER BY messageread ASC
Time before: 1.3478310108185
Time after: 1.3501510620117

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

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

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

Query: SELECT SUM(replycount) AS posts, SUM(threadcount) AS threads FROM forum WHERE parentid=-1
Time before: 1.3565609455109
Time after: 1.358757019043

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

Query: SELECT userid,username FROM user WHERE userid=5893
Time before: 1.3602700233459
Time after: 1.5186769962311

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

Query: SELECT COUNT(*) AS threads FROM thread WHERE dateline > '1077204189' and forumid <> '16' and forumid <> '54'
Time before: 1.5197339057922
Time after: 2.9511320590973

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

Query: SELECT count(*) AS posts FROM post,thread WHERE post.dateline > '1077204189' and thread.threadid=post.threadid and thread.forumid <> '16' and forumid <> '54'
Time before: 2.952183008194
Time after: 3.2847220897675

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

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

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

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

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

Query: SELECT forumid,accessmask FROM access WHERE userid='1'
Time before: 3.3097970485687
Time after: 3.3554339408875

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

Query: SELECT COUNT(*) AS users,session.userid,username,IF(session.userid=0, 2,invisible) AS invisible,usergroupid,forumid
FROM session
LEFT JOIN user ON (user.userid=session.userid)
LEFT JOIN moderator ON (moderator.userid=session.userid)
WHERE session.lastactivity>1077206502
GROUP BY session.userid
ORDER BY invisible ASC, username ASC
Time before: 3.3566598892212
Time after: 3.3748419284821

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

Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'7') ORDER BY replacementsetid DESC,replacementid DESC
Time before: 4.6192750930786
Time after: 5.5579769611359

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

Page generated in 4.3163781166077 seconds with 22 queries,
spending 3.8930354118347 doing MySQL queries and 0.42334270477295 doing PHP things.
Query: DELETE FROM session WHERE sessionhash='' Time before: 7.7143399715424 Time after: 7.9610960483551
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1077206999 WHERE userid='1' Time before: 7.9617660045624 Time after: 8.0490210056305
-----------------------------------------------------------------------------------------------


Any help from you would be highly appreciated! I would live to see my homepage load faster. But i have no clue how to do it :(

[Dark_Wizard]

I just checked your hompage and it was fine.... loaded in 1.16 secs with 17 queries....did you change anything?

[Aaron1]

Hi, wow that's fast! Weird though, because when i check the homepage (not logged in) i get: Page loaded in 7.75 seconds - 7.57% Php - 92.43% MySql with 17 queries.
I think some querie is looping on the frontpage somehow, i am really not sure what's causing the huge delay.

FYI, i have the optimizing forumhome hack from Xenon installed.

[Dark_Wizard]

FYI, i have the optimizing forumhome hack from Xenon installed.

Ahhh, thats your problem...j/k. Anyway I just checked it again and I'm not logged in as well and I still don't see any problems.

Here is what I see:

Page loaded in 1.21 seconds - 44.36% Php - 55.64% MySql with 17 queries.

[Xenon]

maybe your server was just having problems at that time.

regarding your query log:

Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'7') ORDER BY replacementsetid DESC,replacementid DESC
Time before: 4.6192750930786
Time after: 5.5579769611359

Query: SELECT smilietext,smiliepath FROM smilie ORDER BY showid
Time before: 0.58423209190369
Time after: 1.3096740245819


those two queries shouldn't take THAT much time, so i assume either a temporarly server prob, or maybe try to run repair.php to optimize your tables ;)

[Aaron1]

Thanks Xenon will try that, but i am also worried about these queries:

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

Warning: Cannot add header information - headers already sent by (output started at /home/sites/site5/web/forum/global.php:145) in /home/sites/site5/web/forum/admin/functions.php on line 1700
Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='6'
Time before: 0.28320288658142
Time after: 0.28443598747253

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

Query: SELECT count(*) AS posts FROM post,thread WHERE post.dateline > '1077204189' and thread.threadid=post.threadid and thread.forumid <> '16' and forumid <> '54'
Time before: 2.952183008194
Time after: 3.2847220897675
--------------------------------------------------------------

Why is there this warning: cannot add header information?

And the last querie, is for a hack to show how many posts/threads the members since their last visit. But that one is very slow too. Any way to optimize that querie?

[Dark_Wizard]

Make sure there is no white space before the <? at the top of admin/functions.php or after the ?> at the end of the file.

[Xenon]

Well, you can ignore the headers warning.

this will be on every explain=1 page.
Really nothing to worry about :)

As for the second query: well, you can set up an index for the dateline column of the post table, but apart from that there isn't much you can do.
joining the post and thread table is always slow ;)

maybe change the query into:
SELECT count(*) AS posts
FROM post
INNER JOIN thread USING(threadid)
WHERE post.dateline > '1077204189'
AND thread.forumid NOT IN (16, 54)

it could speed up it a little bit.

[Aaron1]

I could try that, thanks!
So you mean instead of: WHERE post.dateline > '1077204189'

you are meaning:

post.dateline > '$bbuserinfo[lastvisit]'

I presume?

[Xenon]

yes, exactly, just without the '

:)

[Aaron1]

Yep, i was figuring out that myself ;-)

I think it's faster in a way, thanks Xenon! But i can't really tell the difference though, because the server load changes every second on my page.

Here's the current result:

---------------------------------------------------------------------
Query: SELECT count(*) AS posts FROM post INNER JOIN thread USING(threadid) WHERE post.dateline > 1077229208 AND thread.forumid NOT IN (16, 54)
Time before: 0.49642205238342
Time after: 0.50070309638977
---------------------------------------------------------------------

[Xenon]

that looks fast enough ;)

[Aaron1]

Enough?
I think you know better than that, because it's never ever fast enough.
And my sorry ass is depending completely on your scripting skills LOL ;-)

[Xenon]

*gg* well but 0.01 sec is ok ;)

[Boofo]

Well, you can ignore the headers warning.

this will be on every explain=1 page.
Really nothing to worry about :)

As for the second query: well, you can set up an index for the dateline column of the post table, but apart from that there isn't much you can do.
joining the post and thread table is always slow ;)

maybe change the query into:
SELECT count(*) AS posts
FROM post
INNER JOIN thread USING(threadid)
WHERE post.dateline > '1077204189'
AND thread.forumid NOT IN (16, 54)

it could speed up it a little bit.
How would you make this query also give you the amount of new threads since last visit along with the posts?

[Xenon]

SELECT count(postid) AS posts, count(DISTINCT threadid) AS threads
FROM post
INNER JOIN thread USING(threadid)
WHERE post.dateline > '1077204189'
AND thread.forumid NOT IN (16, 54)

but it doesn't give you the new threads, just the UPDATED threads

[Boofo]

How would you get new threads started with that?

[Xenon]

well it's doable in one query, but it would reduce the speed of the query alot, so you better devide them up in two queries ;)

also don't use the above query if you do not want to restrict forums, as it's not nessesary for just getting the new posts ;)