Optimizing for Many Tables database instance. For example, to retrieve a BLOB might require a large sequential disk read that is better suited to a traditional hard drive than to an SSD device. •See Section 8.4.2.2, “Optimizing for Character and String Types”  for reasons why a binary  VARCHAR column is sometimes preferable to an equivalent BLOB column. •Rather than testing for equality against a very long text string, you can store a hash of the column value in a separate column, index that column, and test the hashed value in queries. (Use the  MD5()  or CRC32()  function to produce the hash value.) Since hash functions can produce duplicate results for different inputs, you still include a clause  AND  blob_column  =  long_string_value  in the query to guard against false matches; the performance benefit comes from the smaller, easily scanned index for the hashed values. 8.4.3 Optimizing for Many Tables 8.4.3.1 How MySQL Opens and Closes Tables Some techniques for keeping individual queries fast involve splitting data across many tables. When the number of tables runs into the thousands or even millions, the overhead of dealing with all these tables becomes a new performance consideration. When you execute a  mysqladmin status  command, you should see something like this: Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12 The  Open tables  value of 12 can be somewhat puzzling if you have fewer than 12 tables. MySQL is multithreaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. This uses additional memory but normally increases performance. With  MyISAM  tables, one extra file descriptor is required for the data file for each client that has the table open. (By contrast, the index file descriptor is shared between all sessions.) The  table_open_cache  and  max_connections  system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. Many operating systems permit you to increase the open-files limit, although the method varies widely from system to system. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so. table_open_cache  is related to  max_connections . For example, for 200 concurrent running connections, specify a table cache size of at least  200 *  N , where  N  is the maximum number of tables per join in any of the queries which you execute. You must also reserve some extra file descriptors for temporary tables and files. Make sure that your operating system can handle the number of open file descriptors implied by the table_open_cache  setting. If  table_open_cache  is set too high, MySQL may run out of file descriptors and exhibit symptoms such as refusing connections or failing to perform queries. Also take into account that the  MyISAM  storage engine needs two file descriptors for each unique open table. To increase the number of file descriptors available to MySQL, set the  open_files_limit  system variable. See Section B.3.2.16, “File Not Found and Similar Errors” . The cache of open tables is kept at a level of  table_open_cache  entries. The server autosizes the cache size at startup. To set the size explicitly, set the  table_open_cache  system variable at startup. MySQL may temporarily open more tables than this to execute queries, as described later in this section. 1736