Home / Speed Up Queries / Question No: 160

I am not clear about MySQL table cache. Can you explain, what is table cache and when should we use it?

MySQL uses an allocated memory for table caching.  This memory can be increased or decreased by setting variable "table_cache".  For caching, whenever MySQL accesses a table, it places the table in the cache. If an application communicates many tables, it is good to have them in the cache so that data retrieve is faster.

When System needs to increase table cache

MySQL provides open_tables and opened_tables status variables to check whether your system needs to have the table_cache value increased.  However, it is recommended to check it during peak time.

open_tables is the number of tables opened in cache and opened_tables is the total number of tables open.  The default value to table_cache is 64.  Let's consider an example where we need to increase table cache.

table_cache = 64;
mysql> SHOW STATUS LIKE "open%tables%";
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Open_tables   | 64      |
| Opened_tables | 5311545 |
+---------------+---------+

In case of above result, where table_cache has maxed out and opened_tables is high, if you have enough memory, you need to increase table_cache.