CODEWITHSUNDEEP
mysql
Maths Gal
Maths Gal

Reputation: 261

When and how does MySQL caching work?

SELECT SQ_CACHE * FROM TABLE where ID=1

Questions:

Upvotes: 3

Views: 1063

Answers (1)

RobIII
RobIII

Reputation: 8821

  1. Read up on cache-invalidation (here's the first hit I found). MySQL (or any RDBMS) will invalidate (parts of) cached data whenever it needs to. When it actually does this should * not be of your concern; let MySQL (or any RDBMS) handle it. The actual process is (or can be) very complex; that's why this stuff is abstracted away and handled by the RDBMS for you. Same goes for actual storage of data on disk; do you care? Or do you let the RDBMS handle it for you? See? ;-)
  2. See 1.
  3. There you go. (And how is this a question? :P )

* Aside from some corner-cases or bugs maybe.

The "short" answer (note: specific to MySQL) would be: AFAIK MySQL caches on table level granularity; so any change on that table would invalidate all cached results for that table. Other than that, only deterministic queries will get cached, transactions might bypass/invalidate caches, the query cache doesn't support prepared statements, queries must be absolutely exactly be the same for the cache to kick in at all (so an extra space in a query would cause a cache miss) and subqueries aren't supported. And then there are still other factors involved. Maybe some things have changed in more recent versions but these are, some (not all!) of the factors that used to be involved. Correct me if I'm wrong.

Upvotes: 4

Related Questions