InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more

Menu
  • Home
  • Sitemap

Python Programming Language Best Tutorials and Code Examples

Learn Python Right Now!
Home
PHP
Caching Queries and Results in PHP
PHP

Caching Queries and Results in PHP

InfinityCoder December 6, 2016

You don’t want to rerun potentially expensive database queries when the results haven’t changed.

Use PEAR’s Cache_Lite package. It makes it simple to cache arbitrary data. In this case, cache the results of a SELECT query and use the text of the query as a cache key.
Example 10-39 shows how to cache query results with Cache_Lite.
Example 10-39. Caching query results

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
require_once 'Cache/Lite.php';
 
$opts = array(
  // Where to put the cached data
  'cacheDir' => '/tmp/',
  // Let us store arrays in the cache
'automaticSerialization' => true,
// How long stuff lives in the cache
'lifeTime' => 600 /* ten minutes */);
 
// Create the cache
$cache = new Cache_Lite($opts);
 
// Connect to the database
$db = new PDO('sqlite:/tmp/zodiac.db');
 
// Define our query and its parameters
$sql = 'SELECT * FROM zodiac WHERE planet = ?';
$params = array($_GET['planet']);
 
// Get the unique cache key
$key = cache_key($sql, $params);
 
// Try to get results from the cache
 
$results = $cache->get($key);
 
if ($results === false) {
   // No results found, so do the query and put the results in the cache
   $st = $db->prepare($sql);
   $st->execute($params);
   $results = $st->fetchAll();
   $cache->save($results);
}
 
// Whether from the cache or not, $results has our data
foreach ($results as $result) {
   print "$result[id]: $result[planet], $result[sign] <br/>\n";
}
function cache_key($sql, $params) {
   return md5($sql .
              implode('|',array_keys($params)) .
              implode('|',$params));
}

Cache_Lite is a generic, lightweight mechanism for caching arbitrary information. It uses files to store the information it’s caching.

The Cache_Lite constructor takes an array of options that control its behavior. The two most important ones in
Example 10-39 are automaticSerialization, which makes it easier to store arrays in the cache, and cacheDir, which defines where the cache files go.

Make sure cacheDir ends with a /.
The cache is just a mapping of keys to values. It’s up to us to make sure that we supply a cache key that uniquely identifies the data we want to cache—in this case, the SQL query and the parameters bound to it.

The cache_key function computes an appropriate key. After that, Example 10-39 just checks to see if the results are already in the cache.
If not, it executes the query against the database and stuffs the results in the cache for next time.
Note that you can’t put a PDO or PDOStatement object in the cache—you have to fetch results and then put the results in the cache.
By default, entries stay in the cache for one hour. You can adjust this by passing a different value (in seconds) as the lifeTime option when creating a new Cache_Lite object.

Pass in null if you don’t want data to automatically expire. The cache isn’t altered if you change the database with an INSERT, UPDATE, or DELETE query.

If there are cached SELECT statements that refer to data no longer in the database, you need to explicitly remove everything from the cache with the Cache_Lite::clean() method.

You can also remove an individual element from the cache by passing a cache key to Cache_Lite::remove().
The cache_key() function in Example 10-39 is case sensitive. This means that if the results of SELECT * FROM zodiac are in the cache, and you run the query SELECT * from zodiac, the results aren’t found in the cache and the query is run again.

Maintaining consistent capitalization, spacing, and field ordering when constructing your SQL queries results in more efficient cache usage.
A benefit of PHP-layer solutions such as Cache_Lite is that they are database agnostic. However, depending on the database you’re using you may be able to take advantage of database-specific query caching mechanisms.

These kinds of caches, because they are more tightly integrated into the database, are able to be smarter about expiring cached data when it has changed.

For example, you can read about how to enable MySQL’s query cache.

 

Share
Tweet
Email
Prev Article
Next Article

Related Articles

Using DBM Databases in PHP
You have data that can be easily represented as key/value …

Using DBM Databases in PHP

Changing Array Size in PHP
You want to modify the size of an array, either …

Changing Array Size in PHP

About The Author

InfinityCoder
InfinityCoder

Leave a Reply

Cancel reply

Recent Tutorials InfinityQuest

  • Adding New Features to bash Using Loadable Built-ins in bash
    Adding New Features to bash Using Loadable …
    June 27, 2017 0
  • Getting to the Bottom of Things in bash
    Getting to the Bottom of Things in …
    June 27, 2017 0

Recent Comments

  • fer on Turning a Dictionary into XML in Python
  • mahesh on Turning a Dictionary into XML in Python

Categories

  • Bash
  • PHP
  • Python
  • Uncategorized

InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more

About Us

Start learning your desired programming language with InfinityQuest.com.

On our website you can access any tutorial that you want with video and code examples.

We are very happy and honored that InfinityQuest.com has been listed as a recommended learning website for students.

Popular Tags

binary data python CIDR convert string into datetime python create xml from dict python dictionary into xml python how to create xml with dict in Python how to write binary data in Python IP Address read binary data python tutorial string as date object python string to datetime python

Archives

  • June 2017
  • April 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
Copyright © 2021 InfinityQuest - Programming Code Tutorials and Examples with Python, C++, Java, PHP, C#, JavaScript, Swift and more
Programming Tutorials | Sitemap