[PHP] On DB Cache

2008-01-29T16:01:34+00:00

What do you think of my refactoring (and my caching comments)?

[PHP] On DB Cache

2008-01-28T20:50:22+00:00

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
}

?>

[PHP] On DB Cache

2008-01-28T17:27:18+00:00

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

DevP

[PHP] On DB Cache

[PHP] On DB Cache

[PHP] On DB Cache