CODEWITHSUNDEEP
mysqlcaching
ChrisInEdmonton
ChrisInEdmonton

Reputation: 4578

MySQL paginating results, cache or nocache?

Using MySQL, I have a number of queries that look like:

SELECT * FROM table_name WHERE some_clause ORDER BY id LIMIT 1000 OFFSET 0
SELECT * FROM table_name WHERE some_clause ORDER BY id LIMIT 1000 OFFSET 1000
SELECT * FROM table_name WHERE some_clause ORDER BY id LIMIT 1000 OFFSET 2000
SELECT * FROM table_name WHERE some_clause ORDER BY id LIMIT 1000 OFFSET 3000

and so on. The key point is that I am paginating through the result set. Each offset will only ever be executed once, so there's no point in caching it. However, I will eventually iterate through all the results. I care very much about polluting our query cache in this situation.

If I add SQL_NO_CACHE, this will cause MySQL to stop caching the results. Is this the most efficient, as each specific result set will only be used once? Or is MySQL smart enough to cache the entire result set once, pulling subsets based on the limit and offset with each request?

In other words, if I do not use SQL_NO_CACHE, would the four example SQL statements above result in one query and three cached responses, or would it result in four separate uncached queries?

Upvotes: 1

Views: 1229

Answers (1)

ChrisInEdmonton
ChrisInEdmonton

Reputation: 4578

According to MySQL's documentation on query caching, "If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again."

Specifically, the following two statements are considered different, even though they differ only in capitalisation, and so would not be cached:

SELECT * FROM tbl_name
Select * from tbl_name

As such, it is clear that, in my question above, adding SQL_NO_CACHE is the correct thing to do, as MySQL's query cache is useless here.

Upvotes: 1

Related Questions