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 the Author

Kolbe Kegel is a Principal Support Engineer at SkySQL, where he has worked since 2011. 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.
kolbe

Issue is fixed in MariaDB!

This issue initially came to my attention when a SkySQL customer reported that he was seeing problems with query cache utilization. I was able to identify that it was related to partitioning and to this change in behavior made by Oracle. I escalated the issue to MariaDB and they had a fix in MariaDB along with custom binaries for the customer in just a few days. I'm glad to see that they're planning on including the fix in the next release of MariaDB: http://blog.mariadb.org/the-query-cache-and-partitions/

 

Kolbe

kolbe
kolbe

Changed commit message...?

I'm not quite sure why, but the bazaar commit message at http://bazaar.launchpad.net/~mysql/mysql-server/5.5/revision/2661.803.1 is different now than what it was when I posted this entry. Here's the commit message I see from the bzr command-line client:

$ bzr log -r 2661.803.1
------------------------------------------------------------
revno: 2661.803.1
committer: Mattias Jonsson
branch nick: b11761296-5.1_no_qc
timestamp: Mon 2012-02-20 22:59:11 +0100
message:
Bug#11761296: 53775: QUERY ON PARTITIONED TABLE RETURNS CACHED
RESULT FROM PREVIOUS TRANSACTION

The current Query Cache API is not fully compatible with
the partitioning engine.

There is no good way to implement support for QC due to:
1) a static callback for ha_partition would need to have access
to all partition names and call the underlying callback for each
[sub]partition with the correct name.
2) pruning would be impossible, even if one used the ulonglong
engine_data due to if engine_data is changed, the table is
invalidated by the QC.

So the only viable solution to avoid incorrect data is to not allow
caching of queries using partitioned tables.

(There are some extra changes, due to removal of \r as line break)