Name
ANALYZE TABLE
Synopsis
ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLEtable
[,...
]
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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.