How MySQL Does Caching

MySQL has the following caches.

Key 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. (mysys/mf_keycash.c)

Record Cache

This is used for quick scanning of all records in a table. (mysys/mf_iocash.c and isam/_cash.c)

Table Cache

This holds the most recently used tables. (sql/sql_base.cc)

Hostname Cache

For quick lookup (with reverse name resolving). This is a must when you have a slow DNS. (sql/hostname.cc)

Privilege Cache

To allow quick change between databases, the last used privileges are cached for each user/database combination. (sql/sql_acl.cc)

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.