MySQL has the following caches. (Note that the some of the filenames contain an incorrect spelling of the word “cache.”)
A shared cache for all B-tree index blocks in the different NISAM
files. Uses hashing and reverse linked lists for quick caching of
the most recently used blocks and quick flushing of changed
entries for a specific table.
This is used for quick scanning of all records in a table.
This holds the most recently used tables.
For quick lookup (with reverse name resolving). This is a must
when you have a slow DNS. (
To allow quick change between databases, the last used privileges
are cached for each user/database combination.
Heap Table Cache
Many uses of
GROUP BY or
DISTINCT cache all found rows in a
HEAP table. (This is a very quick in-memory
table with hash index.)
Join Buffer Cache
For every “full join” in a
SELECT statement the rows found are cached
in a join cache. (A “full join” here means there
were no keys that could be used to find rows for the next
table in the list.) In the worst case, one
SELECT query can use many join caches.