Performance Tuning in MySQL with Query Caching

When you run a SELECT query, MySQL “remembers” both the query and the results it returns. This is accomplished by storing the result set in a special cache (called the query cache) each time a SELECT query is executed. Then, the next time you ask the server for the same query, MySQL will retrieve the results from the cache instead of running the query again. As you can imagine, this speeds up the process considerably.

Although enabled by default, you must always verify that query caching is turned on, which can be done by checking the server variables. The following example illustrates:

mysql> SHOW VARIABLES LIKE '%query_cache%';

6 rows in set (0.00 sec)

• The first variable, have_query_cache, indicates the server was configured for query caching when it was installed (the default).
• The query_cache_size variable indicates the amount of memory allotted for the cache in bytes. If this value is 0, query caching will be off.
• The values for the query_cache_type variable range from 0 to 2. A value of 0 or OFF indicates that query caching is turned off. ON or 1 means that query caching is turned on, with the exception of SELECT statements using the SQL_NO_CACHE option. DEMAND or 2 provides query caching on demand for SELECT statements running with the SQL_CACHE option.
• The query_cache_limit variable specifies the maximum result set size that should be cached. Result sets larger than this value will not be cached.

You can alter any of these variables using the SET GLOBAL or SET SESSION statements, as shown:

mysql> SET GLOBAL query_cache_size = 16777216;
Query OK, 0 rows affected (0.00 sec)

To see for yourself what impact the query cache is having on performance, run the same query with and without query caching to compare the performance difference. Here’s the version without using the query cache:

mysql> SELECT SQL_NO_CACHE r.RouteID, a1.AirportCode, a2.AirportCode,
    -> r.Distance, r.Duration, r.Status FROM route AS r,
    -> airport AS a1, airport AS a2
    -> WHERE r.From LIKE a1.AirportID
    -> AND r.To LIKE a2.AirportID
    -> AND r.RouteID IN
    ->   (SELECT f.RouteID
    ->   FROM flight AS f, flightdep AS fd
    ->   WHERE f.FlightID = fd.FlightID
    ->   AND f.RouteID = r.RouteID
    ->   AND fd.DepTime BETWEEN '00:00' AND '04:00');

2 rows in set (0.21 sec)

 

Now perform the same query with the cache:

mysql> SELECT SQL_CACHE r.RouteID, a1.AirportCode,
    -> a2.AirportCode, r.Distance, r.Duration, r.Status FROM
    -> route AS r, airport AS a1, airport AS a2
    -> WHERE r.From LIKE a1.AirportID
    -> AND r.To LIKE a2.AirportID
    -> AND r.RouteID IN
    ->   (SELECT f.RouteID
    ->   FROM flight AS f, flightdep AS fd
    ->   WHERE f.FlightID = fd.FlightID
    ->   AND f.RouteID = r.RouteID
    ->   AND fd.DepTime BETWEEN '00:00' AND '04:00');

Dramatic improvements in performance aren’t unusual if query caching is enabled on frequent queries.

CAUTION

Once a table is changed, the cached queries that use this table become invalid and are removed from the cache. This prevents a query from returning inaccurate data from the old table. While this makes query caching much more useful, a constantly changing table won’t benefit from caching. In this situation, you might want to consider eliminating query caching. This can be done by adding the SQL_NO_CACHE option, as previously shown, to a SELECT statement.