Internal Temporary Table Use in MySQL MySQL closes an unused table and removes it from the table cache under the following circumstances: •When the cache is full and a thread tries to open a table that is not in the cache. •When the cache contains more than  table_open_cache  entries and a table in the cache is no longer being used by any threads. •When a table-flushing operation occurs. This happens when someone issues a  FLUSH TABLES statement or executes a  mysqladmin flush-tables  or  mysqladmin refresh  command. •Tables not currently in use are released, beginning with the table least recently used. •If a new table must be opened, but the cache is full and no tables can be released, the cache is temporarily extended as necessary. When the cache is in a temporarily extended state and a table goes from a used to unused state, the table is closed and released from the cache. When the table cache fills up, the server uses the following procedure to locate a cache entry to use: A  MyISAM  table is opened for each concurrent access. This means the table needs to be opened twice if two threads access the same table or if a thread accesses the table twice in the same query (for example, by joining the table to itself). Each concurrent open requires an entry in the table cache. The first open of any  MyISAM  table takes two file descriptors: one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is shared among all threads. If you are opening a table with the  HANDLER  tbl_name  OPEN  statement, a dedicated table object is allocated for the thread. This table object is not shared by other threads and is not closed until the thread calls  HANDLER  tbl_name  CLOSE  or the thread terminates. When this happens, the table is put back in the table cache (if the cache is not full). See Section 13.2.4, “HANDLER Statement” . To determine whether your table cache is too small, check the  Opened_tables  status variable, which indicates the number of table-opening operations since the server started: If the value is very large or increases rapidly, even when you have not issued many  FLUSH TABLES statements, increase the  table_open_cache  value at server startup. 8.4.3.2 Disadvantages of Creating Many Tables in the Same Database 8.4.4 Internal Temporary Table Use in MySQL 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. In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs. The server creates temporary tables under conditions such as these: •Evaluation of  UNION  statements, with some exceptions described later. 1737 mysql>  SHOW GLOBAL STATUS LIKE ’Opened_tables’; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 2741 | +---------------+-------+