MySQL 5.1 Reference Manual  /  ...  /  Disadvantages of Creating Many Tables in the Same Database 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 permitted in the table cache.

Download this Manual
User Comments
  Posted by on March 24, 2004
Here is more information on table cache resizing, see "table_cache":
  Posted by Kumar S on November 20, 2004
you can set the table_cache value in the my.cnf file by
set-variable = table_cache=8
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
  Posted by Stewart Smith on April 3, 2005
Filesystem directory-lookup issues are moot on filesystems with efficient directory indexes (such as XFS).

You may still need to tune table_cache though.
Sign Up Login You must be logged in to post a comment.