SQL Query Caching
By Ori Staub
Caching SQL Query Results
About the Author
Intended AudienceThis tutorial is intended for the PHP programmer interested in caching SQL queries to reduce the overhead of a database connection and query, and to improve script performance.
OverviewMany sites use a database backend as a data store for the site. Whether the database contains product information, category structure, articles or a guest book, some of the data is likely to be quite static and will greatly benefit from a caching system.
Such a system would cache the results of an SQL query into a file stored on the system and hence improve the response time by avoiding the need to make a database connection, forming the query, executing it and retrieving the results.
On systems where the database does not reside on the same machine as the web server and requires a remote connection (TCP or similar), or where large amounts of data are retrieved from the database, you stand to gain even more in terms of response times and resources used.
PrerequisitesThis tutorial will use MySQL as the database. You will need MySQL installed (available from www.mysql.com) and PHP MySQL extension enabled (it is enabled by default).
You will need to know the basics of the SQL (Structured Query Language) in order to query the database.
Caching SQL Query Results
Why cache query results?Caching query results can dramatically improve script execution time and resource requirements.
Caching SQL results also allows you to carry out post processing on the data. This may not be possible if you use file caching to cache the outputs of the entire script (HTML output caching).
When you execute an SQL query, the typical process undertaken is:
Although persistent connections may improve the overhead of connecting to the database, they are more memory intensive and the overall time saved will be very little if a large amount of data is retrieved.
Creating an SQL QuerySQL (Structured Query Language) queries are used as an interface to manipulate a database and its contents. SQL can be used to define and edit the table structure, insert data into the tables, update and delete information from the tables.
SQL is the language used to communicate with the database and in most PHP database extensions (MySQL, ODBC, Oracle etc), the extension manages the process of passing the SQL query to the database.
In this tutorial, only the select statement is used to retrieve data from the database. This data is cached and later used as the data source.
Deciding when to update the cacheCaching can take a few forms according to the program's needs. The 3 most common approaches are:
Caching the resultsThe basics to caching is using the
In this example, an e-commerce store is used. The store has 2 basic tables, categories and products. While product information may change daily, categories remain fairly static.
For product display, you can use an output caching script to store the resultant HTML output in a file to be called up. However, categories may need some post processing. For example, all categories are displayed and according to the category_id variable that is passed to the script ($_REQUEST['category_id']) you may wish to highlight the current category selected.
The categories table has the following format:
+----------------------+------------------+-----+----------------+ | Field | Type | Key | Extra | +----------------------+------------------+-----+----------------+ | category_id | int(10) unsigned | PRI | auto_increment | | category_name | varchar(255) | | | | category_description | text | | | +----------------------+------------------+-----+----------------+In this example, the time limited caching technique is used where the cached SQL output is considered outdated after a set amount of time. In this particular example, 24 hours are used.
Looking at the sql_cache.txt file, it may look something like this:
This output is the internal representation of the variables and their types. In this case you are using
Using the CacheIn order to use the cache, you will need to
You can read the contents of the sql_cache.txt file into a variable using the
Please note: This function is available in PHP version 4.3.0 and above only. If you are using an older version of PHP, a simple workaround is using the
Note that the
Putting it all togetherThe decision whether to cache is time based in this instance. If the file modification timestamp is greater than the current time less the expiration time set, the cache is used, else the cache is updated.
About the AuthorOri Staub is a senior systems analyst, developer and consultant specializing in web-based solutions. He can be contacted at firstname.lastname@example.org