MySQL : Cache

 

Following caches are generally available in MySQL.

 

Key Cache

This is a shared cache for all B-tree index blocks which are in different MYI files. Internally, this cache is using hashing and reverse linked lists algorithm for quick caching for most recently used blocks and flushed the blocks which are not recently used or some data has been changed for particular block.

 

Table Cache

This is used for caching recently used tables. Here, table cache means not complete table structure with data will be stored. Only table structure will be stored in the cache. We can set table_cache parameter with the value of how many tables we want to keep in cache.

 

Hostname Cache

This is very useful cache specially when we have a slow DNS. It will cache the hostname with reverse name resolving. We have two solutions to resolve the slow DNS. Either disable DNS lookup with –skip-name-resolve parameter or increase the host_cache_size value and recompile mysqld.

 

Privilege Cache

This is used for caching used privileges with each user and database combination. It will allow user to change databases quickly. Due to this cache, while add/change/remove user, we are running “flush privileges” command.

 

Heap Table Cache

Generally, this is used by GROUP BY or DISTINCT queries. The rows will be stored in this cache. As it will be reside in memory with hash index, its very fast.

 

Join Buffer Cache

All rows of SELECT statement which is used “Full Join”, will be cached in this buffer cache. Sometime one select query can use many join caches. Here “Full Join” means join type will be ALL or Index where no possible keys can be used to find rows from the next table.

 

We can set this buffer by setting join_buffer_size system variable. It will be allocated when mysql need to do a full join between two tables and it will be freed when query is done. MySQL will store only used columns in the join buffer not whole rows.