function db_cache_2($query)
{
// Query cache table
$sql = 'SELECT * FROM `query_cache` WHERE `query` = \''.mysql_escape_string($query).'\' LIMIT 1';
$qry = mysql_query($sql) or die('Cache Query Error::: '.mysql_error());
// No cache
if(mysql_num_rows($qry) < 1)
{
$qry2 = mysql_query($query) or die('Query error');
$i = 0;
while($row = mysql_fetch_assoc($qry2))
{
foreach($row as $key=>$val)
{
$arr[$i][$key] = $val;
}
++$i;
}
$sql2 = 'INSERT INTO `query_cache` (`cached`, `query`, `results`) VALUES (now(), \''.mysql_escape_string($query).'\', \''.serialize($arr).'\')';
mysql_query($sql2) or die('Cache Insert Error');
}
else
{
// Cache avail
$row = mysql_fetch_assoc($qry);
// Check the file updates
$sql2 = 'SELECT `updated` FROM `ftp_updates` ORDER BY `updated` DESC LIMIT 1';
$qry2 = mysql_query($sql2) or die('Cache Compare Query Error::: '.mysql_error());
$row2 = mysql_fetch_row($qry2);
// Out of date - Update the cache
if(convert_datetime($row['cached']) < convert_datetime($row2[0]))
{
$qry3 = mysql_query($query) or die('Query error');
$i = 0;
while($row3 = mysql_fetch_assoc($qry3))
{
foreach($row3 as $key=>$val)
{
$arr[$i][$key] = $val;
}
++$i;
}
$sql2 = 'UPDATE `query_cache` SET `cached` = now(), `query` = \''.mysql_escape_string($query).'\', `results` = \''.serialize($arr).'\' WHERE `id` = \''.$row['id'].'\'';
mysql_query($sql2) or die('Cache Update Error');
}
else
{
// Cache Valid
// Would have put this above the if - but I think the unserialize would slow things down more than the else statement would.
// Plus I would have to blank out the arr to make sure the array was correct in the end (No old data).
$arr = unserialize($row['results']);
}
}
return($arr);
}
Refactorings
No refactoring yet !
DevP
January 28, 2008, January 28, 2008 17:27, permalink
I'm curious: what sort of app is this for? I'm surprised at a caching DB results in the DB (though if we're talking about really rough queries anyway, I suppose it could make sense).
ellisgl.myopenid.com
January 28, 2008, January 28, 2008 17:33, permalink
It was for a gmaps mashup. I actually made one better that store the output of what I did with the results. All and All.. Just doing the query each time was faster than the caching... Unless I was pulling 10K of records --- of course the initial caching of 10K records took upward of a minute.
DevP
January 28, 2008, January 28, 2008 20:50, permalink
This is my first time trying to refactor stuff!
- I took for granted the overall approach, though I have my doubts (see below).
- I didn't test this, but I think I got the idea across.
- I'm assuming the Cache Table has a uniqueness constraint for "query".
- I did collapse some of the error messages together when it seemed worth it for conciseness.
- I was in a "functions with long English names" mood, rather than an "object-oriented" mood. Not sure what's up with that.
As I said above: I think the actual caching approach has some problems. The serializing/unserializing of a big array like that might hurt, and moreover putting a cache into the DB (when the DB is probably the bottleneck) doesn't seem right. So I'd suggest:
- caching less info, in a less structured way (so: just a list of ids that you can do an easy query on to get the results)
- put these cached results elsewhere, like on the filesystem
- once we're talking about non-db caching: caching outputs makes more sense
<?php
function get_query_results($sql)
{
if (!($results = get_query_results_from_cache($sql)))
{
$results = get_query_results_from_db($sql);
put_query_results_in_cache($sql, $results);
}
return $results;
}
function get_query_results_from_db($sql)
{
$query = mysql_query($sql) or die('Query error');
$arr = array();
while($row = mysql_fetch_assoc($query))
{
$result = array();
foreach($row as $key=>$val)
{
$results[$key] = $val;
}
$arr[] = $result;
}
return $arr;
}
function get_query_results_from_cache($sql)
{
$update_time_sql = 'SELECT `updated` FROM `ftp_updates` ORDER BY `updated` DESC LIMIT 1';
$cache_sql = 'SELECT * FROM `query_cache` WHERE `query` = \''.mysql_escape_string($sql).'\' AND cached>('.$update_time_sql.') LIMIT 1';
$cache_qry = mysql_query($cache_sql) or die('Cache Error::: '.mysql_error());
$row = mysql_fetch_row($qry);
if (!empty($row) && !empty($row['results']))
{
return unserialize($row['results']);
}
else
{
return false;
}
}
function put_query_results_in_cache($sql, $results)
{
//NOTE: this assumes that the query has field has a unique key constraint
$cache_sql = 'INSERT INTO `query_cache` (`cached`, `query`, `results`) VALUES (now(), \''.mysql_escape_string($sql).'\', \''.mysql_escape_string(serialize($arr)).'\') ON DUPLICATE KEY UPDATE cached=now(), query=VALUES(query), results=VALUES(results)';
mysql_query($cache_sql) or die('Cache Update Error'); // same message for any failed insert/update into the cache
}
?>
ellisgl.myopenid.com
January 28, 2008, January 28, 2008 21:21, permalink
Here's the 2nd one I did that I was talking about earlier.
<?php
function convert_datetime($str)
{
list($date, $time) = explode(' ', $str);
list($year, $month, $day) = explode('-', $date);
list($hour, $minute, $second) = explode(':', $time);
$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
return $timestamp;
}
function db_cache_2($query)
{
// Query cache table
$sql = 'SELECT * FROM `query_cache` WHERE `query` = \''.mysql_escape_string($query).'\' AND `type` = \'2\' LIMIT 1';
$qry = mysql_query($sql) or die('Cache Query Error::: '.mysql_error());
// No cache
if(mysql_num_rows($qry) < 1)
{
$qry2 = mysql_query($query) or die('Query error');
$i = 0;
while($row = mysql_fetch_assoc($qry2))
{
foreach($row as $key=>$val)
{
$arr[$i][$key] = $val;
}
++$i;
}
$x = (count($arr)-1);
for($y=0; $y<=$x; ++$y)
{
$a .= 'HTML'
}
$sql2 = 'INSERT INTO `query_cache` (`cached`, `query`, `results`, `type`) VALUES (now(), \''.mysql_escape_string($query).'\', \''.$a.'\', \'2\')';
mysql_query($sql2) or die('Cache Insert Error');
}
else
{
// Cache avail
$row = mysql_fetch_assoc($qry);
// Check the file updates
$sql2 = 'SELECT `updated` FROM `ftp_updates` ORDER BY `updated` DESC LIMIT 1';
$qry2 = mysql_query($sql2) or die('Cache Compare Query Error::: '.mysql_error());
$row2 = mysql_fetch_row($qry2);
// Out of date - Update the cache
if(convert_datetime($row['cached']) < convert_datetime($row2[0]))
{
$qry3 = mysql_query($query) or die('Query error');
$i = 0;
while($row3 = mysql_fetch_assoc($qry3))
{
foreach($row3 as $key=>$val)
{
$arr[$i][$key] = $val;
}
++$i;
}
$x = (count($arr)-1);
for($y=0; $y<=$x; ++$y)
{
$a .= 'HTML';
}
$sql2 = 'UPDATE `query_cache` SET `cached` = now(), `query` = \''.mysql_escape_string($query).'\', `results` = \''.$a.'\' WHERE `id` = \''.$row['id'].'\'';
mysql_query($sql2) or die('Cache Update Error');
}
else
{
// Cache Valid
// Would have put this above the if - but I think the unserialize would slow things down more than the else statement would.
// Plus I would have to blank out the arr to make sure the array was correct in the end (No old data).
$a = $row['results'];
}
}
return($a);
}
?>
DevP
January 29, 2008, January 29, 2008 16:01, permalink
What do you think of my refactoring (and my caching comments)?
ellisgl.myopenid.com
January 29, 2008, January 29, 2008 16:08, permalink
Looks pretty good - haven't tested it thou.
Skudd
May 24, 2008, May 24, 2008 05:00, permalink
Why not provide a more flexible solution? I have a (horrible) implementation of a flexible caching MySQL class that stores the results as a serialized associative array in a file named according to the MD5 hash of the query itself. If I can make it less specific to the application I use it in, I'll share it here.
ellisgl.myopenid.com
May 24, 2008, May 24, 2008 09:13, permalink
I actually opted not to used the code after some after thoughts and test. While yes it worked, I think the overhead was a bit too much for my liking and ended up not using it. I never did get to work on the the original scope of why I even wrote that code.
It's a DB caching scheme that compares the cache to the last update of the db.