ANALYZE TABLE

Use this MySQL statement to store information that can be useful later when the MySQL optimizer chooses the order for consulting indexes during a query.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table[, ...]

Explanation

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.

Examples

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 MySQL 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 MySQL 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=ulfthewolf -p --analyze --all-databases

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