Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 7 Optimization :: 7.4 Optimizing Database Structure :: 7.4.9 Disadvantages of Creating Many Tables in the Same Database
7.5 Optimizing the MySQL Server »
Section Navigation      [Toggle]

7.4.9. Disadvantages of Creating Many Tables in the Same Database

If you have many MyISAM or ISAM tables in a 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

Posted by [name withheld] on March 24 2004 12:31pm[Delete] [Edit]

Here is more information on table cache resizing, see "table_cache": http://www.mysql.com/doc/en/Server_system_variables.html

Posted by Kumar S on November 20 2004 12:32am[Delete] [Edit]

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

Posted by Stewart Smith on April 3 2005 11:49pm[Delete] [Edit]

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.