cache mysql results and requery only if something changes

Question

Is it possible to query the database only once while the page loads in PHP. Somehow store these results and display it to user on subsequent reports. Is there any way to determine if something is changed in the database so that we can connect to DB only then to fetch new/changed results?

Please give me some idea. Thanks.

Answer 1

Check out MySQL Query Cache.

MySQL will simply return the same resultset stored in memory when there is no change required.

http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

Answer 2

You could use additional field in your data table which shows, for example, last record modification timestamp. Then you could use memcache (or another caching subsystem) and save into it fetched database result with last modification timestamps.

In the only one database query you could select this field (using light SQL query with described field in select statement) and compare it with fetched from DB value. If they are different you know to get the new data from DB with complex queries.

Example of "light" SQL query:

SELECT MAX(last_modification) AS last_modification FROM data_table

Answer 3

A Cache, like APC, memcached or Xcache usually has a lifetime before it gets stale. There won't be any calls to the database whatsoever as long as the cache is not stale. Note that no calls are preferable over cached recordsets from the database, because no calls save the entire roundtrip.

If your application is the only writing source to your database, you can manually force the cache to expire before it's set lifetime whenever someone writes to the database. Subsequent queries would then recache when the query is run.

If there is other sources, e.g. remote servers, you'd have to implement a trigger inside the database that informs all applications using the database of the change. A message queue is best used for this.

Answer 4

You can use a caching system to store your data.

On each object modification, you can delete it from cache or refresh its cache version if it is likely to be used.

There are numerous ways of implementing a cache in PHP, among which, as mentioned, Memcached.

Answer 5

There is a function from here, that wraps any other function, caching the results:

function cache_function($buildCallback, array $args= array (), $timeoutMinutes= 60) {
    if (is_array($buildCallback)) {
        $cacheKey= get_class($buildCallback[0]) . $buildCallback[1] . ':' . implode(':', $args);
    } else
        $cacheKey= $buildCallback . ':' . implode(':', $args);

    $file_path= CACHE_PATH . md5($cacheKey);
    @ $file_time= filemtime($file_path);
    $rebuild= $file_time < time() - ($timeoutMinutes * 60);

    //$rebuild= false; // FORCE REBUILD SKIP for WINDOWS

    if ($rebuild or (!$rebuild and !is_readable($file_path))) {
        $build= call_user_func_array($buildCallback, $args);
        file_put_contents($file_path, serialize($build), LOCK_EX);
        return $build;
    }
    return unserialize(file_get_contents($file_path));
}

Also see:

http://blog.digitalstruct.com/2008/02/27/php-performance-series-caching-techniques/