MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Speed Up Queries / Question No: 160

What is table cache in MySQL?

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

Answer No: 160

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.

Related MySQL FAQs to the Above FAQ

What-is-better-query-to-update-a-table What is better query to update a table?

How-to-get-all-dates-between-two-dates How to get all dates between two dates?

How-to-speed-up--multi-table-based-delete-query How to speed up multi table based delete query?

Why-my-update-query-works-slow-in-MySQL Why my update query works slow in MySQL?

What-is-query-cache-in-MySQL What is query cache in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.