The cache of open tables can grow to a maximum of
64; this can be changed with with the
-O table_cache=# option to
mysqld). A table is never closed, except when the cache is full and
another thread tries to open a table or if you use
When the table cache fills up, the server uses the following procedure
to locate a cache entry to use:
Tables that are not currently in use are released, in least-recently-used
If the cache is full and no tables can be released, but a new table needs to
be opened, the cache is temporarily extended as necessary.
If the cache is in a temporarily-extended state and a table goes from in-use
to not-in-use state, it is closed and released from the cache.
A table is opened for each concurrent access. This means that
if you have two threads accessing the same table or access the table
twice in the same query (with
AS) the table needs to be opened twice.
The first open of any table takes two file descriptors; each additional
use of the table takes only one file descriptor. The extra descriptor
for the first open is used for the index file; this descriptor is shared
among all threads.
If you have many files in a directory, open, close and create operations will
be slow. If you execute
SELECT statements on many different tables,
there will be 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 making the table cache larger.