Query caching

xenon · 01-29-2008, 12:41 PM

I've discovered (well, technically, never built a caching system before) a nifty way to cache queries. I was working on the API for my CMS (actually, it's more like a general development framework) and this idea just came to me and I thought it'd be nice to share it with you. So, I'll let the code speak for itself

A cacheable API function:

PHP Code:


			
class API
{
    // [declarations, inits, stuff like that...]
    
    public function getPagesList()
    {
        //---------------------------------------------------------------
        // If the current query has not been cached, cache it now
        //---------------------------------------------------------------
        if( API_Cache::getPagesList() === null )
        {
            $this->db->select( array( 'what' => '*',
                                  'from' => 'sb_pages',
                                  'orderby' => 'p_order ASC' ) );
            
            if( $this->db->get_rows_number() > 0 )
            {
                API_Cache::setPagesList( $this->db->get_results() );
            }
            else
            {
                API_Cache::setPagesList( array() );
            }
        }
        
        return API_Cache::getPagesList();
    }
}

The caching class:

PHP Code:


			
class API_Cache
{
    private static $pages_list;
    
    protected final function __construct() { }
    
    public static function setPagesList( $value )
    {
        self::$pages_list = $value;
    }
    
    public static function getPagesList()
    {
        return self::$pages_list;
    }
}

So, any ideas or smth? What do you think of this method?

You might ask why did I set the cache class constructor as final and protected. Well, I believe that a caching system like this one, should only act as a library where you store values and then retrieve values from. There's no purpose in embedding logic within it, is there now? So, the class can so easily be extended, but the child classes cannot be instantiated, either.

LE: after a benchmark test, on a set of 30 queries (the same query copy-pasted 30 times), the results were:
- without the cache implementation: 0.0160 seconds (on average)
- with cache: 0.0015 seconds

and on a set of 50 queries:
- without: 0.0250 seconds
- with: 0.0017

Which, I believe is pretty good

But I still need to test it with a more complex query, to see how it would really work when the whole system is done (a SELECT * FROM table_name query was used, so...).

wGEric · 01-29-2008, 06:29 PM

That's good if you are doing the same query multiple times on each page load. You should extend it to write to files so that it can cache queries longer than just one page. Of course the data retrieved in these queries can't be changing very often.

Gibou · 01-29-2008, 06:49 PM

Indeed, the idea is interesting but, like wGEric, it's too limited to be very useful. Instead of caching the results of only one query, you could could store several in a hashtable with the query as the key and the resultset as the value... or in two separated arrays with same indexes.

However, thanks for the idea :)

Salathe · 01-29-2008, 07:08 PM

It's a good start but at the moment it doesn't seem very flexible. Being able to have any number of queries cached would be an important improvement. Perhaps also have some checking in there to prevent write statements from being cached (INSERT, UPDATE, etc.) because any results from those should really only be used once.

xenon · 01-29-2008, 08:20 PM

Let me explain why this is useful for me. The snippet above is a part of the API for a system, right? That means it will not change alot. So, in the main page you will have a menu, a footer and a sidebar perhaps. This is where the caching kicks in. 3 queries would need to be executed in order to have the list of pages in all the 3 places mentioned above. Then, perhaps a developer would need to add the links in another 2 places (like in the header and in a box somewhere on the website). He doesn't need 2 more additional queries, the system will use the existing one. Plus, there is going to be much more stuff like that going on in this system.

Personally, I wouldn't cache the queries like I would the pages because the queries may always change. Imagine a query changing, the page cache being regenerated (the html file), but still using the old query. You end up with a bunch of broken pages (until the SQL cache is being regenerated). Impractical.