Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to store information that can be useful later when the MySQL optimizer chooses the order for consulting indexes during a query. Multiple tables can be specified in a comma-separated list. The statement works on MyISAM and InnoDB tables. Unless the NO_WRITE_TO_BINLOG option is given, the statement is written to the binary log file and will be executed by slaves if using replication. The LOCAL option is synonymous with this option. For MyISAM tables, this statement places a read lock on the tables; for InnoDB, a write lock. This statement requires SELECT and INSERT privileges. Here is an example:

ANALYZE TABLE workreq;

+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| workrequests.workreq | analyze | status   | OK       |
+----------------------+---------+----------+----------+

The message type in the results can be status, error, info, or warning. If the table hasn’t changed since it was last analyzed, the message text will read, “Table is already up to date” and the table won’t be analyzed.

This statement is equivalent to using myisamchk --analyze at the command line for MyISAM tables. To analyze all tables (MyISAM and InnoDB), you can use the mysqlcheck utility from the command line like so:

mysqlcheck --user=russell -p --analyze --all-databases

If you want to see the stored key distribution that the ANALYZE TABLE statement creates, execute the SHOW INDEXES statement.