What is a lot of MySQL queries per page?

December 5th, 2007, 4:54 pm

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).

December 5th, 2007, 4:55 pm

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: [ Download ] [ 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: [ Download ] [ 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.  

December 5th, 2007, 5:07 pm

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.

December 7th, 2007, 10:28 am

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!?

December 7th, 2007, 12:30 pm

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.

December 8th, 2007, 4:50 pm

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.

July 29th, 2008, 6:19 pm

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]

August 1st, 2008, 12:30 am

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?

August 1st, 2008, 12:50 pm

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]

August 1st, 2008, 1:22 pm

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]

August 1st, 2008, 1:48 pm

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: [ Download ] [ 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.

August 1st, 2008, 5:23 pm

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