Like others we were not satisfied with the fix for a bug in MySQL which caused the query cache and partitioning to not work reliably together. The bug, in simple terms, was that if the query cache was enabled and you used partitioned tables and if a partitioned table was using a transactional engine like InnoDB or XtraDB, the query cache could, under certain circumstances, return incorrect results.
Returning incorrect results is a definite, high-priority bug. However, the upstream fix was to disable all caching of queries from partitioned tables. We wanted a better solution because the query cache can be very useful and beneficial for partitioned tables, just like it is useful and beneficial for non-partitioned tables.
The root of the problem was that the query cache did not have any visibility into partitioned tables. In particular it didn’t know anything about a given table’s storage engine, including if the table was transactional or not. This lack of information prevented the query cache from intelligently caching and returning the cached results of queries.
We solved this by creating a way for the query cache to talk to the underlying storage engine of a partitioned table to see if it is:
- OK to cache the result of a query
- OK to return a cached result
With that information in hand the query cache can now properly cache new queries and provide correct cached results for duplicate queries every time, no matter if the table is partitioned (or not), or transactional (or not).
The patch is already in the MariaDB 5.5 source on Launchpad and will be in our next release of MariaDB 5.5.
Update: Monty has also written about this on his blog.