Caching SQL Results to Improve MySQL Performance

When it’s not possible to remove SQL statements that are unnecessary and the rate of change of common data is relatively low, caching SQL results can provide a significant performance boost to your application and enable additional scalability of your database server.

MySQL Caching

The MySQL query cache can provide a boost in performance for a high read environment and can be implemented without any additional application overhead. The following is an example using the profiling functionality to show the execution time and the individual complexity of a regular SQL statement and a subsequent cached query:

SET GLOBAL query_cache_size=1024*1024*16;
SET GLOBAL query_cache_type=1;
SET PROFILING=1;
SELECT name FROM firms WHERE id=727;
SELECT name FROM firms WHERE id=727;
SHOW PROFILES;

As you can see, the second query is 10 times faster. You can use the profiling functionality to provide a detailed analysis of individual steps:

mysql> SHOW PROFILE FOR QUERY 1;

17 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 2;

6 rows in set (0.00 sec)

As you can see, the second query performs significantly less work.

The impact of the MySQL query cache can degrade the performance for environments with heavy write to read ratios. This is due to the coarse nature of the query cache, where a change of any data to a given table will result in an invalidation of all SQL statements that are cached that use the table. In an environment with a high number of reads and writes, this can result in significant invalidations of SELECT statements that do not gain the benefit of ever being used.

Application Caching

Adding caching at the application level can dramatically reduce unnecessary SQL statement execution. There is no single recommended technique for application caching. Strategies include caching the results of a given SQL statement in memory or in local files, or it can involve lazy object instantiation in the application object relational mapping (ORM) layer. The implementation should reflect the needs of your application. For example, the following pseudo code would replace a simple SELECT with:

// Existing SELECT
SELECT FROM TABLE
RETURN VALUE
// Cached SELECT
GET FROM CACHE
IF EMPTY
  SELECT FROM TABLE
  PUT INTO CACHE
END
RTURN VALUE

What is necessary is that any changes to the table are also reflected in the cache. For example, the following pseudo code is required:

INSERT/UPDATE/DELETE FROM TABLE
REMOVE FROM CACHE

Alternatively, you could enable a time-to-live (TTL) value for cached values if your caching system allows periodic cache invalidation.

The popular Memcached product (refer to http://memcached.org/) is widely used for many applications. This caching infrastructure can be easily integrated with many popular programming languages.

There are different ways to implement a MySQL/Memcached data synchronization for invalidation. One implementation is with an integration of the MySQL User Defined Functions (UDFs) for Memcached combined with database triggers to manage invalidations. Another option is to process the MySQL binary log events for SQL statements and then appropriately manage the synchronization with the implementing cache infrastructure.