innodb_thread_concurrency  option, up to approximately 32 for a high-powered modern system. Increase the value for the innodb_concurrency_tickets  option, typically to 5000 or so. This combination of options sets a cap on the number of threads that InnoDB  processes at any one time, and allows each thread to do substantial work before being swapped out, so that the number of waiting threads stays low and operations can complete without excessive context switching. 

## 8.5.10 Optimizing InnoDB for Systems with Many Tables 

•If you have configured non-persistent optimizer statistics (a non-default configuration), InnoDB computes index cardinality values for a table the first time that table is accessed after startup, instead of storing such values in the table. This step can take significant time on systems that partition the data into many tables. Since this overhead only applies to the initial table open operation, to “warm up”  a table for later use, access it immediately after startup by issuing a statement such as SELECT 1 FROM tbl_name  LIMIT 1 . 

Optimizer statistics are persisted to disk by default, enabled by the innodb_stats_persistent configuration option. For information about persistent optimizer statistics, see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters” . 

## 8.6 Optimizing for MyISAM Tables 

The MyISAM  storage engine performs best with read-mostly data or with low-concurrency operations, because table locks limit the ability to perform simultaneous updates. In MySQL, InnoDB  is the default storage engine rather than MyISAM . 

## 8.6.1 Optimizing MyISAM Queries 

Some general tips for speeding up queries on MyISAM  tables: 

•To help MySQL better optimize queries, use ANALYZE TABLE  or run myisamchk --analyze  on a table after it has been loaded with data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1.) MySQL uses this to decide which index to choose when you join two tables based on a nonconstant expression. You can check the result from the table analysis by using SHOW INDEX FROM tbl_name  and examining the Cardinality  value. myisamchk --description --verbose  shows index distribution information. 

•To sort an index and data according to an index, use myisamchk --sort-index --sort- records=1  (assuming that you want to sort on index 1). This is a good way to make queries faster if you have a unique index from which you want to read all rows in order according to the index. The first time you sort a large table this way, it may take a long time. 

•Try to avoid complex SELECT  queries on MyISAM  tables that are updated frequently, to avoid problems with table locking that occur due to contention between readers and writers. 

• MyISAM  supports concurrent inserts: If a table has no free blocks in the middle of the data file, you can INSERT  new rows into it at the same time that other threads are reading from the table. If it is important to be able to do this, consider using the table in ways that avoid deleting rows. Another possibility is to run OPTIMIZE TABLE  to defragment the table after you have deleted a lot of rows from it. This behavior is altered by setting the concurrent_insert  variable. You can force new rows to be appended (and therefore permit concurrent inserts), even in tables that have deleted rows. See Section 8.11.3, “Concurrent Inserts” . 

•For MyISAM  tables that change frequently, try to avoid all variable-length columns ( VARCHAR , BLOB , and TEXT ). The table uses dynamic row format if it includes even a single variable-length column. See Chapter 16, Alternative Storage Engines . 

