Cache mysql results and require only if something changes

Question

Is it possible to query the database only once while the page is loading in PHP. Somehow save these results and display them to the user in subsequent reports. Is there a way to determine that something has changed in the database so that we can connect to the database only to get new / changed results?

Please give me some idea. Thanks.

Answer 1

Check out MySQL Query Cache.

MySQL will simply return the same result set that will be stored in memory when no changes are needed.

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

Answer 2

A cache, such as APC, memcached, or Xcache usually has a lifetime before it expires. There will be no calls to the database if the cache is not out of date. Please note that no calls are preferable to cached recordsets from the database, because no calls save the entire callback.

If your application is the only write source in your database, you can manually force caching to complete before it sets a lifetime when someone writes to the database. Subsequent requests will then be repeated when the request is executed.

If there are other sources, for example. remote servers, you will have to implement a trigger inside the database, which informs all applications using the database changes. For this, it is best to use a message queue.

Answer 3

There is a function 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)); } 

See also:

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

Answer 4

In the data table, you can use an additional field that shows, for example, the timestamp of the last record. Then you can use memcache (or another caching subsystem) and save the database to the result using the latest change timestamps.

In one database query, you can select this field (using the lightweight SQL query with the specified field in the select statement) and compare it with the selected DB value. If they are different, you know, to get new data from the database with complex queries.

An example of a light SQL query:

 SELECT MAX(last_modification) AS last_modification FROM data_table 

Answer 5

You can use the caching system to store your data.

Each time you modify an object, you can remove it from the cache or update its version of the cache if it is likely to be used.

There are many ways to implement the cache in PHP, among which, as already mentioned, Memcached.