How MySQL Does Caching MySQL

MySQL has the following caches. (Note that the some of the filenames contain an incorrect spelling of the word “cache.”)

  • 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.
  • Record Cache
  • This is used for quick scanning of all records in a table. (mysys/mf_iocash.c and isam/_cash.c)
    This holds the most recently used tables. (sql/
  • Hostname Cache
  • For quick lookup (with reverse name resolving). This is a must when you have a slow DNS. (sql/
  • Privilege Cache
  • To allow quick change between databases, the last used privileges are cached for each user/database
    combination. (sql/
  • 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.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

MySQL Topics