2006-07-06

MySQL Query caching

I just recently found out that MySQL 5.x has a Query Cache feature that will cache select statements and their results. In addition, it will track the tables involved and invalidate the caches if the involved tables are updated. From the 5.0 Documentation:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

The query cache is extremely useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

Note: The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

Note: The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.

Setting a query as eligible for caching is done through a directive in the SELECT clause. From the 5.0 Documentation:

Examples: SELECT SQL_CACHE id, name FROM customer; SELECT SQL_NO_CACHE id, name FROM customer;

The documentation for enabling the Query cache mechanism can be found here. Once you get it running, it works quite well. If you use the MySQL Administrator application you can monitor the efficacy of your setup using the "Memory Health" graphs.