I am currently building a PHP framework (original, i know) and im working on some optimisation features for it. One dilema I have come accross is what is the best way to cache MySQL results? I know some people will say, first optimise your MySQL etc, but lets say for arguments sake, my query takes 1 minute to run and is as optimised as possible.
What would be the best way to cache the results in PHP so I dont have to rerun the query every page load?
My first thought was to maybe loop through the results, add them to an array... serialize them and then store in a file. Now as creating the cache only occurs once, I can afford the overhead of the serialize function if the array contains say 1 million results. How ever, loading the cached file and then unserializing the array on every pageload, could have performance impact.
Would it then be a better idea to while caching, instead of serializing the results and the writing to file, write to the file in a way that displays the results in a PHP readable array. So when it loads, there is no unserialize overhead.
Are there any other (read: faster) ways to cache a slow query for frequent use?
If this is a straight array, then you could use var_export() rather than serialize (wrapping it with the appropriate "" and write it to a .php file; then include() that in your script. Best done if you can write it outside the htdocs tree, and only really appropriate for large volumes of data that memory caches would consider excessive.
Memcached.
I always try to create my own solution at least once, to grasp better what's going on under the hood in most situations. When I created my own caching solution, I essentially did what you're talking about.
// serialize an array of all results
$serialzedData = serialize($resultData);
// set TTL (60 seconds) and create cache filename with timestamp
$ttl = 60;
$cacheFilename = $ttl . '_' . time() . '_' . md5($sqlQuery)
// dump
file_put_contents($cacheFilename, $serializedData);
Prior to a query firing, it would search the cache directory for files with a matching query hash. If it does, it tests timestamp + ttl <= current_time, and if true, returns the unserialized file contents. Otherwise, overwrite it.
Mysql caches result of query, may be you should increase mysql query cache size? Or cache result of big query in standalone table?
Google for Memcache, that should put you in the right direction.
