mysql's query cache behaviour on LIMIT

Question

Is it accurate to assume that MySQL's query cache gets checked on subsequent execution of queries in the form SELECT ... WHERE ... LIMIT x, y where the the only varying paramaters in the query are x and y?

Answer 1

Actually... no.

The query cache in MySQL, as I discussed at length in another answer, is an optimization that does only one thing: it returns the exact same result set in response to the exact same query, if the previous result was cached and has not been removed from the cache due to invalidation (by changes to the data in the underlying tables) or pruning to make room for more recent queries.

The query cache is checked before the query reaches the parser, whenever a query comes in starting with the characters "SEL" (case-insensitive).

If the query, byte-for-byte, is not identical to one already cached, the query will not be served from the cache. If anything changes, even a single space in the query, then that's a different query according to the query cache.

If a query with LIMIT X,Y or the equivalentLIMIT ... OFFSET ... is cached, then only the actual rows returned are stored in the query cache, and only the identical query with the same limit and offset will fetch those same rows from the cache again.