Page 1 of 1

Problem caching mysql results

Posted: Wed Jan 20, 2010 8:42 pm
by Hieroglyphics
Hi, I'm building a content management system and I want my query results to be cached to improve page load, so I attempted to make a cache at first without looking at any tutorials and I failed, now I read a few things and made a new one, except now the mysql_query is faster than loading from cache.

Code: Select all

From Query: 0.00079894065856934
From Cache: 0.0017709732055664
Here is my code, of course I took out my other functions in the class because they are unnecessary but the code is below.

Code: Select all

class Raen_Cache {
    protected function writeCache($file, $access, $data) {
        $handle = fopen($file, $access) or die ("Raen Error: Unable to access " . $file);
        fputs($handle, serialize($data));
        fclose($handle);
    }   
 
    protected function readCache($file) {
        if (file_exists($file)) {
            return @unserialize(file_get_contents($file));
        } else {
            $ret = "Raen Error: Cache file does not exist " . $file;
            return $ret;
        }
    }   
}
 
class Raen_DB extends Raen_Cache {
    public function executeQuery($query) {
    
        if (preg_match("/^SELECT/",$query)) {
            $queryKey = md5($query);
            $present = time();
            $cacheFile = "cache/" . $queryKey . ".txt";
            
            if (file_exists($cacheFile)) {
                $fType = 'w';
            } else {
                $fType = 'x';
            }
            
            if (!file_exists($cacheFile) || ($present - 3600) > filemtime($cacheFile)) {
                $result = mysql_query($query) or die(mysql_error());
                
                while ($fetchArray = mysql_fetch_array($result) ) { 
                    $sqlArray[] = $fetchArray; 
                }
                
                $this->writeCache($cacheFile, $fType, $sqlArray);
                    
                return $sqlArray;
            } else {    
                $readCache = $this->readCache($cacheFile);
 
                return $readCache;
            }
        } else {
            $result = mysql_query($query) or die(mysql_error());
            return $result;
        }
    }   
}
Thank you, if you are one of those who reply and attempt to help me :)

Re: Problem caching mysql results

Posted: Wed Jan 20, 2010 8:58 pm
by Eran
MySQL has several levels of caching itself, and frequently accessed data and queries reside in memory - which would always be faster than disk access. Cache query results only when the queries are too expensive to run repeatedly (ie, complete too slow) or the data is too large to fit in memory (unlikely for a simple CMS). Don't cache any SELECT query blindly (like you seem to be doing).
In the case of this particular query there is simply no reason to cache the results.

Re: Problem caching mysql results

Posted: Wed Jan 20, 2010 9:03 pm
by Hieroglyphics
Ohhhhh I see thanks :) I never actually knew that I just wanted to cache them because I figured caching is always faster. So what syntax should I preg_match for deciding whether to cache or not?

Re: Problem caching mysql results

Posted: Thu Jan 21, 2010 2:42 am
by Eran
Don't cache by pattern, decide on it case-by-case - only when you encounter a performance issue and it appears caching would improve it.