[extremesportsonly]

I have a rather large web site programmed in PHP and was wondering how many mySQL queries is too many for a single page?

I realize this question is rather vague and would depend on what is being queried, server resources, etc., but I am tying to streamline the programming so it builds the pages more efficiently.

An example: Currently, there are 161 queries on the home page.

Site is at http://www.extremesportsonly.com

[Man Down]

I checked out your site and it loaded completely in about 5 seconds. I don't think 161 queries is to many as long as the website loads fine. I'm not sure how many queries is to many and it would be impossible to tell since it varies depending on specs of the server being used.

[Zmodem]

161!!! good grief

Perhaps it's time to re-evaluate your code, and optimize optimize optimize.

I think 161 is extremely high for a single page. The *nukes (postnuke, phpnuke, etc) average around 50 queries per page, and that is pretty high.

Your average forum (phpbb, invisionboard) run 12-18 queries per page.

5 seconds to load is not a problem, provided it didn't take 5 whole seconds JUST to query the server. If so, your host is gonna smack you upside the head with a MySQL clue bat. If it took 5 seconds because of Internet lag or browser rendering...fine...

But it's not my site. This is just my 2 cents from a half wit

--cheers

[nd2]

that is allot, what are you doing to use that many querys?

my aim for all my projects is 10 or less no matter what for display (admins whould be a bit diffrent), your database might be faily small at the moment but think if you did a few thousand posts you page whould take a few secounds to render per user that is un-needed over head.

to try and reduce query calls do some pre-rendering where possible even if it's just some of those side lists, if your using mysql templates do some template caching so you dont load the same template twice, i whould aim for 50 querys at first but try and get that down to 25 since your site does does have a quite on screen.

cache your results if you use them more then once like whats said in the mysql documentation, a database is a bottleneck and you should try your very best to only use it when you realy need to.

nice site thow.

[Chris Boulton]

157 queries used. Page build time: 0.2291270 seconds !

Please tell me you arent using a lot of queries inside while loops..thats a killer. You can join then with the JOIN statements..or even better, trick it like

PHP Code:

$query1 = mysql_query("SELECT * FROM blah");

while($blah = mysql_fetch_array($query2)) {

 $blahcache[$blah[id]] = $blah;

}

$query2 = mysql_query("SELECT * FROM blah2");

while($blah2 = mysql_fetch_array($quer2y)) {

 if($blahcache[$blah2[blah_id]]) {

  echo "we cached this!";

 } else {

 echo "there was no item to cache for this";

} 

Instead of:

PHP Code:

$query = mysql_query("SELECT * FROM blah");

while($blah = mysql_fetch_array($query)) {

 $query2 = mysql_query("SELECT * FROM blah2 WHERE blah_id='$blah[id]");

 $blah2 = mysql_fetch_array($query);

 echo $blah2[blah_id];

} 

(Ignore the code...its messy..its late...)

Now, if you are using templates (as mentioned above) you can cache them in something like $templatecache[title] = $contents And then a simple if statement when fetching them will make it so they are not fetched again.

There are lots of other options to consider.. You have to really look into this on the MySQL website.

I usually aim for a maximum of 15 queries (if i am desparate) per page. Most pages of mine use about 11.

(Like the layout as well!)

Chris

[jv2222]

Something to consider is a web cache that serves only the HTML page - unless the database has been updated in some way.

What I have done in the past is to create a special caching table that has three fields.

1) html_page TEXT
2) rebuild BOOL
3) page_id INT

Then the logic is this.

------

User connects...

1) No cache exists for this page or the rebuild flag is set to true.

Then build HTML page into a $str buffer and insert it into the caching table. Echo the buffer.

2) Cache exists for this page and rebuild flag is false.

Just print html_page from the database and exit.

Cheers,
Justin

[extremesportsonly]

Got the number down to 103 Thanks for replies. Hopefully I can cut another half off that, but I don't see how The home page just has lots of stuff ....only 75 - 80 on the other sport pages ...will need to think about the caching idea sooner than I thought, but know absolutely nothing about caching Lots of user interaction, so caching may not be very efficient either.

thanks again!

[jv2222]

If you can't cache whole pages then consider the possibility of caching query results of multiple queries by using..

serialize and unserialize.

Cheers,
Justin.

[Tweakin]

Just keep optimizing slowly to get the number down, as it is quite high (for what I see on the page). Perhaps you could walk us through a few of the queries (maybe a section or so) to provide an idea of how you are approaching things, I am sure you would then get some great input on optimization.

You must be hosted pretty well by the way, those 161 queires took far less then a second to load for me... unless you are miscounting them thats something to keep your head up about :-)

Nice site.

[extremesportsonly]

Speaking of counting the queries, this is how I have it being done.

Top of each page is:

PHP Code:

$querynum = 0; 

And then each query has the querynum++ after it.

PHP Code:

$result = mysql_query( "SELECT ........." ) or error( mysql_error() ); $querynum++; 

This should be effective to count the queries, correct?