3

I have a shared hosting with limited capabilities. Memcache and mod_cache are not available to me. I would like to implement my own PHP caching method to cache the results of load-intensive SQL query. Using summary tables in SQL is not an option because the query takes multiple parameters that can assume thousands of different values (it's a statistical system).

My idea is like this

function execQuery($parameter)
{
     $query = "SELECT ....... " . $parameter; //query code has been generated

     $rand = mt_rand(0,10); 
     //no need to check/save expiry dates,generate cache randomly for speed

     if($rand == 0)
     {
           //execute query normally and generate new cache
           $result = mysql_query($query);
           $md5 = md5($query); // the name of the cache file equals the md5 of the query
           $cache_file = fopen("/cache/" . $md5 ."php", "w");
           fwrite($cache_file, $result); // the result is written as php code generating a ready for use result array, code has to be modified I think
           //return the result
     }
     else
     {
            //cache will be used
            $md5 = md5($query);
            if(file_exists("/cache/" . $md5 ."php"))
            {
                  include("/cache/" . $md5 ."php");
                  return $result;
            }
            else
           {
                  //no cache exists, run query normally and generate cache
           }
     }   
}

What do you think of this and what are the possible pitfalls? What are the implications of using mt_rand, md5? Do you think they will out-perform a load-intensive query? Are the multiple system file writes worth it (only one write for every 10 hits and can be even increased)? Improvements and suggestions are highly welcomed.

1
  • As it sounds like you are looking for more of a design review than a code review, this question belongs on Software Engineering. Commented Jul 13, 2014 at 5:50

1 Answer 1

1

Basically you want to create a unique key for every query that you can use to find corresponding result in cache. So, should be something like

$key = md5(query);

Then you test that key, in your example, if the file "/cache/$key.php" exists the you return the content otherwise run the query.

Of course the cache will go stale, so you also want how old is that entry, you can stat() the file to get the time and unlink() the file if it's too old (e.g. 30s). You also want to have the same cache invalidating mechanism available to parts of the system that update the data so they always get the latest data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.