vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)
-   -   29 queries on Forumhome !!! (https://vborg.vbsupport.ru/showthread.php?t=38471)

Jawelin 05-09-2002 09:01 PM
29 queries on Forumhome !!!
 
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 05-09-2002 11:51 PM
Yeah, he has really done a great job in minimizing queries. He should really join the vBB developer team. ;)

Jawelin 05-10-2002 08:02 AM
FF, could you share some secret with us ? :p

DWZ 05-10-2002 09:11 AM
Or maybe he hard coded it to say 29 querys ;)

Admin 05-10-2002 11:17 AM
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 05-10-2002 08:34 PM
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 ??? ]
:p

Hop'in you ! ;)
Thnx

Jawelin 05-17-2002 04:53 PM
Quote:
Hop'in you ! ;)
Thnx
:p

Kyomu 05-17-2002 09:06 PM
You just quoted yourself...

*inconspicuosly bumps*

Jawelin 05-18-2002 06:36 AM
If you'd prefer, I could quote the above (post #6) question... nobody answerded...
:p

Admin 05-18-2002 06:43 AM
There we go, 1 down, 28 more to go! :D

Admin 05-18-2002 07:03 AM
Oh look, 27!

Jawelin 05-18-2002 07:06 AM
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 05-18-2002 07:11 AM
Oh my god, 25!

Admin 05-18-2002 07:14 AM
I don't believe this, 23!

What do you mean Jawelin?

Jawelin 05-18-2002 10:07 AM
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 05-18-2002 11:24 AM
How about showing me your index page, so I can see where all these queries are coming from.

Jawelin 05-19-2002 11:27 AM
1 Attachment(s)
Quote:
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 05-19-2002 11:28 AM
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 05-19-2002 11:32 AM
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 05-19-2002 01:43 PM
Quote:
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 05-19-2002 01:48 PM
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 05-20-2002 07:59 AM
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 05-20-2002 11:52 AM
Thanks for the $templatesused hint - saved me quite a few queries on my index, forumdisplay and showthread! :)

MattR 05-26-2002 01:46 AM
You can tie the 3 PM queries into one if you're good. :D

Admin 05-26-2002 10:18 AM
I did that Matt. :)

PHP Code:
  $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 05-26-2002 02:02 PM
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 05-26-2002 02:57 PM
Quote:
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 05-26-2002 06:51 PM
Quote:
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 05-27-2002 10:59 AM
Thank Matt. :)

Jawelin 05-27-2002 01:59 PM
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 06-05-2002 01:53 PM
Quote:
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 :
Code:
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 06-05-2002 01:59 PM
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/showt...threadid=62319

Quote:
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 06-23-2002 04:11 AM
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 08-03-2002 11:43 AM
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 08-03-2002 11:50 AM
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 ;)


All times are GMT. The time now is 02:45 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01306 seconds
  • Memory Usage 1,806KB
  • 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
  • (1)bbcode_code_printable
  • (1)bbcode_php_printable
  • (7)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (35)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete