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):

  1. 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.
  2. 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.
  3. 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:

  1. It is case sensitive – changing even one letter case will produce different hash key.
  2. 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.

MySQL Quiz
4 Comments
  1. Hi,

    Perhaps the most obvious limitation to query cache is its global mutex, effectively serializing access to the cache.
    A lot has been written on this.

    There are many performance issues with using the cache. Try http://www.mysqlperformanceblog.com/2009/03/19/mysql-random-freezes-could-be-the-query-cache/#comment-516145.

    The next big limitation, as I see it, is the naive invalidation mechanism, which blindly removes queries from the cache, that could have been kept there, due to global per-table invalidation.

    Regards

  2. MarkSpizer says:

    great post as usual!

  3. Robin says:

    Hello,Ask a question.
    How to expand mysql query cache?
    Only add physical memory? memcached can not cache SQL results.

  4. emt training says:

    nice post. thanks.

Leave a Reply