July 7, 2012

Heads up! No more query cache for partitioned tables as of MySQL 5.5.23.

A customer opened an issue recently to ask why the query cache wasn't working after he upgraded to MySQL 5.5.25. The reason really ended up surprising me. As of MySQL 5.5.23, the Query Cache is disabled for partitioned tables! This is a "fix" for bug #53775. At first I thought perhaps the fix for the bug had resulted in the query cache being inadvertently disabled for partitioned tables, but the comments that go along with the commit make it pretty clear that disabling the query cache was the intended "fix". You can review the commit message and the code changed at revision 2661.803.1 in the MySQL Server 5.5 repository. I agree with Mikael Ronstrom, who wrote on bug #53775, "Disabling the query cache for partitioned tables is a bit too harsh of a solution." However, the change in behavior is not nearly as harsh as the complete failure to update the documentation to reflect this pretty serious change in functionality. Not even the entry in the MySQL 5.5.23 release notes makes any mention of the effect of the change, only a small statement about the initial bug report. There's no change to How the Query Cache Operates, which explains conditions under which queries are not cached, nor to Restrictions and Limitations on Partitioning, which explains some perhaps unexpected side-effects of using Partitioning (I think the complete unavailability of query caching would qualify). So if you're seeing lower query cache utilization and you use partitioned tables, this change could very well be the explanation you're looking for.

About Kolbe Kegel

Kolbe Kegel is the MariaDB Enterprise Tech Lead at MariaDB Corporation. Kolbe has worked with MySQL since 2005, first at MySQL, later at Sun Microsystems after its acquisition of MySQL Inc., then at Oracle after its acquisition of Sun.

Read all posts by Kolbe Kegel