Best approach to limit database traffic on large query?

Question

I have a database that i'm dealing with which is updated every few hours remotely (not on any specific time span) and i have no control over the administration of it. I have web clients connecting to it to view information contained within it. These clients (coded using PHP and Javascript) might be checking the database very often (impatient users) even though there may not be any change to the database itself and the check will involve quite a lengthy query involving lots of lookups and cross referencing, etc.

So in order to cut down on database queries and to keep things snappy what would be the best way to limit the number of times the clients will actually run the whole query on the database?

To make things crystal clear, i have no way of altering the database, i can only query it. But i have full control over the source of the web client.

Answer 1

You should use some sort of cache. For more serious sites, take a look at memcached. If you are in a smaller scale, cache the results to file by serializing it.

Answer 2

Do you have control over the web app? If users are logged in and have sessions associated with them, you could cache the time since a user last queried the database, and refuse to hand back new data if a request is beneath some threshold interval since the last query.

PHP Sessions

Answer 3

I would take a look at Zend_Cache.

Each query will only run once for the lifetime of the cache (which can be easily changed). Sessions aren't a viable solution since the same query will run at least once per user. Sessions can also be easily renewed.

You'll want to check if the result set exists in the cache. If not, cache the result set with a unique identifier:

$query = "SELECT * FROM really_big_table";
$cache_query = md5($query);
if(!$result = $cache->load($cache_query))
{
     $result = $db->fetchAll($query);
     $cache->save($result, $cache_query);
}

By using a cache you are setting your own time frame for when data is updated for all users.