Problem caching mysql results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Hieroglyphics
Forum Newbie
Posts: 13
Joined: Wed Jan 20, 2010 8:25 pm

Problem caching mysql results

Post 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 :)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Problem caching mysql results

Post 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.
Hieroglyphics
Forum Newbie
Posts: 13
Joined: Wed Jan 20, 2010 8:25 pm

Re: Problem caching mysql results

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Problem caching mysql results

Post 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.
Post Reply