The fading of Query Cache limitations
There is no question about the MySQL Query Cache greatness.
The MySQL Query Cache feature started his way with some limitations. However, in time, there were a lot of improvements (fixing) for those limitations.
One should test his MySQL version with the Query Cache limitations before using it.
Here are some improved limitations I found (at MySQL 5.0 version and above):
- The limitation: Query comments are used as part of the query cache hash key. This means that different comments will (probably) produce different query cache hash keys.
The improvement: The existence of the comment in the query is important but not their contents. This means that even empty comment affects the query cache hash key, but two same queries that different with their comment content will have the same query cache hash key. - The limitation: Queries that begin with spaces or comments are ignored by the Query Cache mechanism.
The improvement: The Query Cache mechanism ignores spaces and comments at the beginning and at the end of the query. - The limitation: Query Cache does not support prepared statements.
The improvement: MySQL Query Cache does work well with prepared statements.
However, MySQL Query Cache still have the following “textual changes” limitations:
- It is case sensitive – changing even one letter case will produce different hash key.
- Extra spaces within the query (not at the beginning) will produce different hash key.
I would love to hear from you about more improvements to the MySQL Query Cache mechanism.
Ilan Hazan.