29 queries on Forumhome !!!

[Jawelin]

FireFly, I saw you managed to keep such a low number of queries, even having almost all the index.php hacks I have on my board... (and got problems to mantain below 40 qrs...) !!

What's your secret ??? :confused:

Thanks.

[Kyomu]

Yeah, he has really done a great job in minimizing queries. He should really join the vBB developer team. ;)

[Jawelin]

FF, could you share some secret with us ? :p

[DWZ]

Or maybe he hard coded it to say 29 querys ;)

[Admin]

Goddamnit DWZ, stop telling all my secrets! :D

29 is not that low, I wish it was less, like 25 or so. I might take it down to 27 later today, I have some ideas... :)

[Jawelin]

For me it's low enough... :)
I though to OUTER JOIN some query...

But could you share some of your secrets about (just like the forum viewers on home...) or at least give us some precious trick ?

I'm very eager for mysql optimisation.... !!! And I absolutely need it.

As an O-T example, right now I multiplexed the getpermissions() function per each user against one forum, obtaining strange values after 4500+ queries in 18 secs.... Ehehehehhhh!
[do you remember our old thread / my old idea about a complete and more accurate permission buster (https://vborg.vbsupport.ru/showthread.php?postid=218315#post218315) ??? ]
:p

Hop'in you ! ;)
Thnx

[Jawelin]

Hop'in you ! ;)
Thnx
:p

[Kyomu]

You just quoted yourself...

*inconspicuosly bumps*

[Jawelin]

If you'd prefer, I could quote the above (post #6) question... nobody answerded...
:p

[Admin]

There we go, 1 down, 28 more to go! :D

[Admin]

Oh look, 27!

[Jawelin]

Well. My bumps made you workin' harder !!! :D

Now a simple question: how to count more totals all at once in the same query ?
:p

[Admin]

Oh my god, 25!

[Admin]

I don't believe this, 23!

What do you mean Jawelin?

[Jawelin]

As I told you, my questions made you working on optimizing queries.

The obvious followin question is : could you give me/us/everybody any hint to keep all the hacks you have installed on the index.php page and, same time, join together some queries to make their total number much lower ?

Nakkid said 40 queries are absolutely too much. Now I have 39-42 depending on some forumbit features...
I absolutely would drop them down !

:p

Thank you

[Admin]

How about showing me your index page, so I can see where all these queries are coming from.

[Jawelin]

Originally posted by FireFly
How about showing me your index page, so I can see where all these queries are coming from.
Sure.
Here's my index.php 'query collection' (queries only, of course), as you requested.

[Jawelin]

Just a curiosity about templates (performance issue), I just posted in vb.com too:
I understood that, if I include templates in "$templatesused", they will be loaded at page loading time, saving a query later when they would be actually used.
1) Is this query actually skipped or simply is not mentionned in querycount ?
2) Should I pre-load all templates, even their execution is if-conditioned ? What could be the right measure to choose whether or not ?
3) Is there a memory overload when I charge it with all templates ? If I evaluate them only when actually used, they will be unloaded right after or will stay in mem ?

Thanks.

[Admin]

You want to load all queries once, in $templatesused. Then when gettemplate() is called, it won't call the database but get the template from the cache, which is created at the beginning.
ALWAYS use $templatesused, you have no idea how helpful it is.

I meants to see your index.php?explain=1 page, it will tell me more.

[Jawelin]

Originally posted by FireFly
You want to load all queries once, in $templatesused. Then when gettemplate() is called, it won't call the database but get the template from the cache, which is created at the beginning.
ALWAYS use $templatesused, you have no idea how helpful it is.

I meants to see your index.php?explain=1 page, it will tell me more.
Thanks for the tip about templates. I'll do it, even for if-conditioned templates ... :squareeyed:

Sorry: didn't understand about index.php... Btw, I'm using vb 2.2.5 with the updated db_mysql.php and /index.php?explain=1 doesn't work any more....
:hurt:

Thanks again

[Admin]

Send me your index.php by email and I'll take a look. You must have a query inside a loop which is how you get to 40.

[Jawelin]

It's comin'... Actually I don't think there's any recursive or iterative call... but I'm sending it to you.

LMK

Thanks a lot.
Bye

[Erwin]

Thanks for the $templatesused hint - saved me quite a few queries on my index, forumdisplay and showthread! :)

[MattR]

You can tie the 3 PM queries into one if you're good. :D

[Admin]

I did that Matt. :)

$allpm=$DB_site->query_first("SELECT COUNT(*) AS messages,
SUM(IF(dateline>$bbuserinfo[lastvisit] AND folderid=0,1,0)) AS newpm,
SUM(IF(messageread=0 AND folderid=0,1,0)) AS unreadpm
FROM privatemessage WHERE userid=$bbuserinfo[userid] $ignoreusers");

$newpm['messages']=$allpm['newpm'];
$unreadpm['messages']=$allpm['unreadpm'];
Well I copied it from somewhere, don't really remember right now. But the problem is the query is slower for users with lots and lots of PM's (like myself), but considering most people don't I thought it would be wiser to just use that one query.

[Erwin]

You're right - I reduced my index.php queries from 52 to 33 using all the stuff I learnt from here, WITHOUT reducing the number of hacks I have. ;)

[MattR]

Originally posted by FireFly
Well I copied it from somewhere, don't really remember right now. But the problem is the query is slower for users with lots and lots of PM's (like myself), but considering most people don't I thought it would be wiser to just use that one query.

I think I suggested something like that on the vB Dev forum. At least with Sybase it is significantly faster than the 3 queries and with less I/O.

[FWC]

Originally posted by FireFly
Well I copied it from somewhere, don't really remember right now.I just copied it from here. Thanks, Chen! :)

[Admin]

Thank Matt. :)

[Jawelin]

I suddendly grabbed your precious trick !
:D

Well; I would use the same SUM(IF()) or COUNT(IF()) constructs on tables like post or thread, to reduce the query count when I check for posts since a time, per user, and so on...
(note: I built indexes on those 'time' fields)

My question is: should a COUNT(IF()) clause much heavy than three or forur different SELECTS, on such a large tables ?

Thanks

[Jawelin]

Originally posted by Jawelin
I suddendly grabbed your precious trick !
:D
My question is: should a COUNT(IF()) clause much heavy than three or forur different SELECTS, on such a large tables ?


I tried to join all my queries, for example, on post table, with the tip of SUM(IF ...,1,0) AS ....
This way, with a simple alone query, I dropped down by four the counter.
Before going on with actions also on most other tables, I'm here to ask if such a small performance decrease :
Before:
Page generated in 0.34648 seconds (35.27% PHP v4.1.2 + 64.73% MySQL v3.23.45) with 36 queries.

After:
Page generated in 0.49735 seconds (23.61% PHP v4.1.2 + 76.39% MySQL v3.23.45) with 32 queries.

could be a problem ???
We are on forumhome, so I should consider to multiply this load for all the concurrent user number.

What would be worst ?
0.15 secs load overhead or 4 queries more ???
:laugh:

Thank you very much.
Bye

[MattR]

Find out what the I/O cost is and you'll have your answer. Query time is not a good indicator of total cost.

http://www.sitepointforums.com/showthread.php?s=&threadid=62319


But that requires a significant amount of I/O, meaning that the MySQL engine will have to perform many disk reads (disk I/O), load it into memory (logical I/O) and throw it to your PHP client (typically over the network so network I/O). Add those together along with in the PHP overhead and you get the total IO for the query.

[Velocd]

Interesting reading ;)

I've known about the $templatesused listing for awhile now, and how it can reduce queries. I'm defiantly planning on going through most of my PHP files making sure all templates eval( 'd
are listed in this area. The problem with alot of hacks out there on vb.org, mostly the ones directly affecting index.php, and showthread.php, do not add their templates in the $templatesused area :(

So anyway, good tips, although I have only been able to understand one so far out of this thread, and thats using $templatesused. What is this other tip?--To merge queries or something?

[KuraFire]

I would still like to know how FF got so few queries on the index.php :(

He has 2-3 more hacks installed on forumhome yet about 10 queries less than I have :(

FF, can't you just _share_ your optimizations? PLEEAAASE? :D

[Xenon]

I think that would be hard...
I don't know the modifycations, but i think he has changed lots of codelines, so explaining wood be hard, and posting files isn't allowed ;)