What are a lot of MySQL queries per page?

  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

On my site some of my pages are pushing 100 queries per page load. I'm just wondering what would be considered too many MySQL queries on a page. I try to minimize query usage as much as possible but I was just wondering.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

I try to keep mine down to one per page. Max 3 or 4 if it is complicated. Most of the data on your page could probably be precalculated and put into another table.

What will you do when you get 50,000 visitors a day? Will you have 100*50,000 or 5,000,000 mysql queries per day?
  • cjxxi
  • Expert
  • Expert
  • cjxxi
  • Posts: 565
  • Loc: Fort Worth, TX

Post 3+ Months Ago

PunkPuke wrote:
On my site some of my pages are pushing 100 queries per page load. I'm just wondering what would be considered too many MySQL queries on a page. I try to minimize query usage as much as possible but I was just wondering.


wow that is a lot of queries, are you using a super duper software application of something
  • Prime
  • Professor
  • Professor
  • User avatar
  • Posts: 935
  • Loc: Liverpool

Post 3+ Months Ago

100 queries per page load is truly horrific :shock: :? I've never seen a page perform that many queries. Have you looked at 'join' statements :?: They may help. You're sooooooooooooo far over the mark it's hard to over-state.

I recently spent a day rewriting a class because it was performing 8 queries for a specific page load and that was deemed unacceptable.

Cheers, Prime ... :D
  • maxxximus
  • Beginner
  • Beginner
  • maxxximus
  • Posts: 42
  • Loc: UK

Post 3+ Months Ago

Have to echo what has already been said.

Its difficult as you don't give any idea of the type of queries or what kind of application but perhaps using more advanced SQL statements might reduce this load. Also maybe think about caching your results.

You can cache a single result to a text file using the serialize function and file_put_contents . Or you could cache the whole page if the page content returned is the same for each visitor.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_union.asp
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

First of all I am very thankful to all of those who replied to my question. Thanks a lot guys. I just want to clarify my question again. I'm talking about how many times I call the mysql_query() function. Not how many times the function is physically programmed into the page. Just incase you thought I meant the other way.

spork wrote:
http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_union.asp


Yeah I use those when I can usually, like I would never have 2 queries right next to each other looping out data. In fact I have 1 query that gets data from 8 tables at once! I think the main problem though is the way my site is setup for easy programming is I have functions that usually do a lot of seperate database calls and put them into a loop of a MySQL query or something a lot. For example on my site every user has a user ID, and I have a function that gets their nickname and for the sake of easiness when I'm programming instead of joining the tables I usually just end up using the function for the nickname cause it performs a bunch of other things and so if I change something that has to do with it, then I don't have to change all 200 pages of my site. But this is just one small example and I actually try to not call that function as often as possible and it's not one of my more worst case problems. A lot of times I will need to perform entirely seperate queries inside of the result loop of another query.

maxxximus wrote:
Also maybe think about caching your results.

You can cache a single result to a text file using the serialize function and file_put_contents . Or you could cache the whole page if the page content returned is the same for each visitor.


Is that really effective? I mean it's kind of like storing data into a database. It seems it would take a lot of processing power too. I see how it would be useful for a ton of queries. Do you know of a class of some sort for this I could checkout? I've never seen it before on such a large scale.

The pages on my website are setup with a header and a footer with a side table. My menu is dynamic and has a lot of database calls on it's own along with some more in my main page included in all the pages. If I create a new blank page on my website with the side menu (which has an AJAX messenger on it that I made) then there is 38 queries on avg done.

But without the side table, a blank page with the menu and header and all that is about 21 queries usually. I guess it's not as bad as a hundred. But for example on my forums, on a topic that is 2 pages big, and if I'm showing 1 full page (as big as you can get), there are 186 queries! (that's probably the most on my site though)

I know that is still a lot, but my site is pretty heavy. It's not a pretty design website. I use a database class and when I'm logged in as an admin I have it print out the number of queries that have run on the page. I'm wondering if any of you guys do that? You honestly only get 8 or less queries per page? Do you physically count them or actually use a statistics script that gives you that information?

I was doing a database query everytime I called my own version of a date() function which among many other things includes getting the users GMT and DST settings and instead I called it once in my main script and saved it and it went from 62 queries on my main page to 47... And that forum topic view page went from 186 to 149 queries. Only problem is I can't do many other things as easily. I just had an idea though to track my redundant queries easily. In my query() function of my database class I'm going to have it store all queries made into an array, then if I'm logged in have it display all queries at the bottom of the page.

I'm gonna have a lot of fun doing optimization for the next few months...
  • champi0n
  • Graduate
  • Graduate
  • User avatar
  • Posts: 199

Post 3+ Months Ago

hmmmm thats a ton of queries. It's gonna be hard to go thru and get rid of un-needed queries and such
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

Image
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

lol, that flowchart is kinda hard to read. The only problem is if I did that, every time it would be NO & NO, so it would be useless because my pages are always dynamic. I'm not sure how you'd implement that. Like for example I have a memberslist page that lists users accounts from the database. Can I cache that? What if someone new joins? I'd still have to do a query every page load to see if someone joined. Which makes it pointless. Unless I'm not getting something... :scratchhead: *yawn need sleep*
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

When someone new joins, a static profile page should be generated. When they update their profile, re-generate that static HTML page.

When someone new joins, re-generate pages with numbers effected by that new person joining.

When content is updated, regenerate all pages effected by that content change.

You want to avoid generating content dynamicly for the visitor as much as possible.
If a page gets updated once every 60 seconds but you have 60 people per-second looking at that page, there's potentially alot of wasted effort in generating the page dynamicly each time.

AJAX makes it easier to leave statistics like who's online & stuff that should generally be considered "live content" out of pages. A system dedicated to sessions can be queried for that type of information.

It's not easy, I'm just barely leaning on the surface here, haven't even scratched it.

The flowchart should be considered transitional.
  • champi0n
  • Graduate
  • Graduate
  • User avatar
  • Posts: 199

Post 3+ Months Ago

Maybe you can provide an example of your header/menu code that has the 21 something queries.

Perhaps you're query is on the wrong side of a loop?
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

The 21 queries is now down to 14 queries (8 unique tables), and only a couple even have a loop and in those cases it only outputs the result, and the query is above the loop.

champi0n wrote:
Maybe you can provide an example of your header/menu code that has the 21 something queries.


haha, that's a lot of code for me to post. Maybe you could provide an example of caching a dynamic content page, because I'm too stupid to figure out what you mean (slow learner :( ). However I assume that's a lot of code too.
  • Prime
  • Professor
  • Professor
  • User avatar
  • Posts: 935
  • Loc: Liverpool

Post 3+ Months Ago

Can you post your database table definitions :?:
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

$userlist = mysql_cached("SELECT * FROM users",60); // Get a user list, updated every minute
print_r($userlist);

Code control can be, cached or uncached. As well, there is NO mysql database connect unless the file is not available. Only connects when the file is needed.

Code: [ Select ]

$MYSQL_CACHE_DIR = '/tmp';

// cache_time > 0, normal. cache_time == 0: get and update. cache_time < 0: get and don't save
function mysql_cached($query,$cache_time = -1)
{
  GLOBAL $MYSQL_CACHE_DIR;

  $f = $MYSQL_CACHE_DIR . "/" . md5($query) . '.mysql';
  
  // Find if file exists
  if (($cache_time > 0) && file_exists($f) && ((filemtime($f) + $cache_time) > time()))
  {
    return unserialize(file_get_contents($f));
  }
  else
  {
    // Connect to database
    bd_connect();    

    // query
    $rawdb = mysql_query($query);

    if (($rawdb) && ($rawdb != 1))
    {
      if (mysql_num_rows($rawdb) != 0)
      {
        while ($bd_row = mysql_fetch_array($rawdb, MYSQL_ASSOC))
        {
                   $bd_array[] = $bd_row;
              }
      }
      
      // Free memory
      mysql_free_result($rawdb);
    }
    
    // Write cached file
    if ($cache_time >= 0)
    {
      $fp = fopen($f,'w');
      fwrite($fp,serialize($bd_array));
      fclose($fp);
    }

    return $bd_array;
  }
}
  1. $MYSQL_CACHE_DIR = '/tmp';
  2. // cache_time > 0, normal. cache_time == 0: get and update. cache_time < 0: get and don't save
  3. function mysql_cached($query,$cache_time = -1)
  4. {
  5.   GLOBAL $MYSQL_CACHE_DIR;
  6.   $f = $MYSQL_CACHE_DIR . "/" . md5($query) . '.mysql';
  7.   
  8.   // Find if file exists
  9.   if (($cache_time > 0) && file_exists($f) && ((filemtime($f) + $cache_time) > time()))
  10.   {
  11.     return unserialize(file_get_contents($f));
  12.   }
  13.   else
  14.   {
  15.     // Connect to database
  16.     bd_connect();    
  17.     // query
  18.     $rawdb = mysql_query($query);
  19.     if (($rawdb) && ($rawdb != 1))
  20.     {
  21.       if (mysql_num_rows($rawdb) != 0)
  22.       {
  23.         while ($bd_row = mysql_fetch_array($rawdb, MYSQL_ASSOC))
  24.         {
  25.                    $bd_array[] = $bd_row;
  26.               }
  27.       }
  28.       
  29.       // Free memory
  30.       mysql_free_result($rawdb);
  31.     }
  32.     
  33.     // Write cached file
  34.     if ($cache_time >= 0)
  35.     {
  36.       $fp = fopen($f,'w');
  37.       fwrite($fp,serialize($bd_array));
  38.       fclose($fp);
  39.     }
  40.     return $bd_array;
  41.   }
  42. }
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

Prime wrote:
Can you post your database table definitions :?:


My tables are correctly done trust me. I planned them out and everything using an entity relationship model program.


Yeah now I see what you're talking about with the caching. I didn't really visualize it in my MySQL query function, but that is awesome man. I can write my own version sort of like that for my database class now.

The thing is, I don't see too many queries I'd want to use it on in that header, only like 5. But I know it will come in handy for a ton of queries on my site. Like for example, I have a site configuration table in my database for when I login and want to make changes, I'm sure that could be delayed like 2 to 5 min at the least unless it's something important.

The only thing is a lot of the queries that are in my main include file are user specific and need to be there up to the minute.

I did have some issues with calling the same queries a lot, like this user authentication function that wanted to know the permissions of a user. So I made it so it only called it one time and then stored the permissions in a variable and then if the variable had it in it, it wouldn't call the query again. I had another idea though that would be useful instead of making certain variables for certain queries I thought of just making my mysql_query() function store the result of queries that are done during the current page load into a variable temporarily and then if the same exact query is called before the end of the page load then just use whatever result was stored. Basically the same thing you guys showed me except only for the current page load so I could reduce the possibility of calling the same query for queries that are not normally cached for 2 min or whatever. The only problem is it still has the pitfall of: If in my code I do a SELECT, then update that table, then do the exact same SELECT again before the end of page load, so nvm I don't think that will work... unless I some how unset the saved results variable IF someone updates, deletes or inserts to the same table between selects. Any ideas?

I've never actually heard anything about this stuff before, but it's pretty cool optimization. Any other cool abstract optimization tricks you guys aren't telling me? I taught myself PHP from google, so I highly doubt that I know everything (cept for the database entity relationship notation stuff, learned that from school).
  • maxxximus
  • Beginner
  • Beginner
  • maxxximus
  • Posts: 42
  • Loc: UK

Post 3+ Months Ago

Similar to Camperjohn - a class which first queries DB and writes the query to a cache file using serialize function. No further queries of DB are necessary until cache file expires(default of 1 hour).

PHP Code: [ Select ]
 
// cache.inc
 
class Cache {
 private $name = NULL;// private members only >= PHP 5
 private $value = array();
 private $ttl;
 
 function __construct($name, $ttl = 3600) { // Default $ttl is 3600 (60 minutes until expiry)
   $this->name = $name;
   $this->ttl = $ttl;
 }
 
 function Check() {
   $cached = false;
   $file_name = $this->name . ".cache";
   if (file_exists($file_name)) {
    $modified = filemtime($file_name);
    if (time() - $this->ttl < $modified) {
      $fp = fopen($file_name, "rt");
      if ($fp) {
       $temp_value = fread($fp, filesize($file_name));
       fclose($fp);
       $this->value = unserialize($temp_value);
       $cached = true;
      }
    }
   }
   return $cached;
 }
 
 function Save() {
   $file_name = $this->name . ".cache";
   $fp = fopen($file_name, "wt");
   if ($fp) {
    fwrite($fp, serialize($this->value));
    fclose($fp);
   }
 }
 
 function SetValue($key, $value) {
   $this->value[$key] = $value;
 }
 
 function GetValue($key) {
   if (isset($this->value[$key])) {
    return $this->value[$key];
   }
   else {
    return NULL;
   }
 }
}
 
  1.  
  2. // cache.inc
  3.  
  4. class Cache {
  5.  private $name = NULL;// private members only >= PHP 5
  6.  private $value = array();
  7.  private $ttl;
  8.  
  9.  function __construct($name, $ttl = 3600) { // Default $ttl is 3600 (60 minutes until expiry)
  10.    $this->name = $name;
  11.    $this->ttl = $ttl;
  12.  }
  13.  
  14.  function Check() {
  15.    $cached = false;
  16.    $file_name = $this->name . ".cache";
  17.    if (file_exists($file_name)) {
  18.     $modified = filemtime($file_name);
  19.     if (time() - $this->ttl < $modified) {
  20.       $fp = fopen($file_name, "rt");
  21.       if ($fp) {
  22.        $temp_value = fread($fp, filesize($file_name));
  23.        fclose($fp);
  24.        $this->value = unserialize($temp_value);
  25.        $cached = true;
  26.       }
  27.     }
  28.    }
  29.    return $cached;
  30.  }
  31.  
  32.  function Save() {
  33.    $file_name = $this->name . ".cache";
  34.    $fp = fopen($file_name, "wt");
  35.    if ($fp) {
  36.     fwrite($fp, serialize($this->value));
  37.     fclose($fp);
  38.    }
  39.  }
  40.  
  41.  function SetValue($key, $value) {
  42.    $this->value[$key] = $value;
  43.  }
  44.  
  45.  function GetValue($key) {
  46.    if (isset($this->value[$key])) {
  47.     return $this->value[$key];
  48.    }
  49.    else {
  50.     return NULL;
  51.    }
  52.  }
  53. }
  54.  



Then instantiate the class

PHP Code: [ Select ]
include 'cache.inc';
 
// connect to DB
 
// query DB - $result is the resource
 
//loop thru the recordset and store as multidemensional array $RsArray
 
$RsArray1 = array();
 
while ($rows = mysql_fetch_array($result))
{
$RsArray1[] = $rows;
}
 
// instantiate new instance of Cache class
 
$cache = new Cache('data');
if ($cache->Check()) {  
echo "These values are retrieved from cache \n";
$arr = $cache->GetValue('arr');
 
}
else {      
$arr = $RsArray1;
$cache->SetValue('arr', $arr);
$cache->Save();
echo "These results are retrieved directly from a database query";
$arr = $cache->GetValue('arr');
 
 
}
 
// loop through $arr and echo out fieldname values
for( $i=0; $i <= sizeof($arr); $i++)
{ echo $arr[$i]['fieldname']."\n";
}
 
// free resource
 
mysql_free_result($result);
 
 
 
  1. include 'cache.inc';
  2.  
  3. // connect to DB
  4.  
  5. // query DB - $result is the resource
  6.  
  7. //loop thru the recordset and store as multidemensional array $RsArray
  8.  
  9. $RsArray1 = array();
  10.  
  11. while ($rows = mysql_fetch_array($result))
  12. {
  13. $RsArray1[] = $rows;
  14. }
  15.  
  16. // instantiate new instance of Cache class
  17.  
  18. $cache = new Cache('data');
  19. if ($cache->Check()) {  
  20. echo "These values are retrieved from cache \n";
  21. $arr = $cache->GetValue('arr');
  22.  
  23. }
  24. else {      
  25. $arr = $RsArray1;
  26. $cache->SetValue('arr', $arr);
  27. $cache->Save();
  28. echo "These results are retrieved directly from a database query";
  29. $arr = $cache->GetValue('arr');
  30.  
  31.  
  32. }
  33.  
  34. // loop through $arr and echo out fieldname values
  35. for( $i=0; $i <= sizeof($arr); $i++)
  36. { echo $arr[$i]['fieldname']."\n";
  37. }
  38.  
  39. // free resource
  40.  
  41. mysql_free_result($result);
  42.  
  43.  
  44.  
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

That's a pretty good example too man. I think I've got it down to those 14 queries and then 5 of them will be cached. I think that's reasonable, I don't think there are any extra queries that I can combine. I know how to query multiple tables at once and join and all that.
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

Hey, I implemented that caching system (the first one) and I used it on a query that checks to see if an IP or User Account is banned, but since it's unique data for every user, pretty much it makes a new .mysql cache file for each user/IP. They're only like 1KB each, but you think it would be smart to have like X amount of files per X IP's that visit my website!?
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

What you could do, it then do mysql_cached("SELECT * from BANNED_IPS");

Then see if the users IP is in that array. This would result in ONE cache file from the database. and would not hammer the database when a new user arrives.

This is of course assuming you dont have a million banned users.
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

Yeah I'm an idiot, why didn't I think of that. I'm just not use to doing that kind of stuff when I query. Good idea thx man. Yeah I only have like 1 banned thing right now anyway.

Only thing is, I don't really think I can do that with a lot of my user based queries.
  • deltawing1
  • Born
  • Born
  • deltawing1
  • Posts: 1

Post 3+ Months Ago

How many queries you have depends on the complexity of your application. 100 queries is on the high side, but not particularly huge. I have heard of queries going up to a couple of hundred per page load.

I think Wordpress typically does around 15-20 queries per page load. That is not a lot.
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

deltawing1 wrote:
How many queries you have depends on the complexity of your application. 100 queries is on the high side, but not particularly huge. I have heard of queries going up to a couple of hundred per page load.

I think Wordpress typically does around 15-20 queries per page load. That is not a lot.

Are you serious ... ? wow, I would never have thought it that much ... where did you get this information?
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

It depends how advanced your site is, if you try to cache the queries you can cache and combine queries that you can combine on your site, then you did good in my book regardless of the number. I think I've learned that by now.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Ozzu is around 8-10 queries per page load with 2-3 being cached usually. The more traffic you get every query you can reduce it by helps (as long as you are doing your queries smart still, sometimes less is not better). I use the explain statement to make sure:

http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/ ... plain.html

It is important to look at how many rows are scanned from each table and taking the product of that. That gives you a good idea of how many rows MySQL has to examine to execute the query. The smaller you keep that number the better.

Also the join type is important with system, const, eq_ref, and ref being the best. If you see "All" that is usually not good.

What I recommend if you choose to optimize or reduce your queries is to first figure out how long it takes for your current queries. Then once you make your changes you can see if the overall time goes up or down. If the time increases then you should go back to how you were doing it. If the time is reduced then you would use the new queries.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

PunkPuke wrote:
A lot of times I will need to perform entirely seperate queries inside of the result loop of another query.


I would highly recommend you do not do that ever, as that increases the number of queries on your page dramatically. If your first query returned 30 results, and then you did a separate query using each of those results that is another 30 queries you just added. Ouch.

A better soluttion is to take the results from the first query and store them in an array of some sort. Then you could take the results from that and use it in your 2nd query to get everything you need.

For example, lets say I needed 30 widgets from the database and they each have their unique ID. What you could do is construct your query to first get those 30 ids and store them in an array which we will call "widgetIDArray". Then if you need the colors of the widgets which are stored in another table, you could do something like this for your sql statement:

Code: [ Select ]
 
$sql = 'SELECT color
    FROM WIDGET_COLOR_TABLE
    WHERE widget_id IN (' . join(",", $widgetIDArray) . ')';
  1.  
  2. $sql = 'SELECT color
  3.     FROM WIDGET_COLOR_TABLE
  4.     WHERE widget_id IN (' . join(",", $widgetIDArray) . ')';


I have now effectively reduced the queries from 31 to 2.
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

Thanks for the tips A LOT, I actually took an entire database college course since I started this topic too LOL... I have a lot of optimization to do, A TON.

This thread is worth bookmarking for sure.

THANKS

Post Information

  • Total Posts in this topic: 27 posts
  • Users browsing this forum: No registered users and 86 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.