MySQL 5.4 Reference Manual :: 7 Optimization :: 7.4 Optimizing Database Structure :: 7.4.9 Disadvantages of Creating Many Tables in the Same Database
Section Navigation [Toggle]
- 7.4 Optimizing Database Structure
- 7.4.1 Make Your Data as Small as Possible
- 7.4.2 Column Indexes
- 7.4.3 Multiple-Column Indexes
- 7.4.4 How MySQL Uses Indexes
- 7.4.5 The MyISAM Key Cache
- 7.4.6 The InnoDB Buffer Pool
- 7.4.7 MyISAM Index Statistics Collection
- 7.4.8 How MySQL Opens and Closes Tables
- 7.4.9 Disadvantages of Creating Many Tables in the Same Database
If you have many MyISAM tables in the same
database directory, open, close, and create operations are slow.
If you execute SELECT statements
on many different tables, there is a little overhead when the
table cache is full, because for every table that has to be
opened, another must be closed. You can reduce this overhead by
increasing the number of entries allowed in the table cache.
User Comments
Here is more information on table cache resizing, see "table_cache": http://www.mysql.com/doc/en/Server_system_variables.html
you can set the table_cache value in the my.cnf file by
set-variable = table_cache=8
Or
If you have the SUPER privilege then, use
set GLOBAL table_cache=8;
since the Variable 'table_cache' is a GLOBAL variable it should be set with SET GLOBAL
-Kumar@guyfromchennai.com
Filesystem directory-lookup issues are moot on filesystems with efficient directory indexes (such as XFS).
You may still need to tune table_cache though.
Add your own comment.