MySQL InnoDB statistics gathering (analyze table)

Posted by David Yahalom under MySQL

I’ve seen many MySQL DBAs completely unaware of the fact that like most modern RDBMS systems, MySQL also relies on using statistics for keeping track of data distribution in tables and for optimizing join statements.

The syntax for analyzing a table for statistics is very simple:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE <em class="replaceable"><code>tbl_name</code></em> [, <em class="replaceable"><code>tbl_name</code></em>] ...

• You must hold insert and select privileges on the table you want to analyze.
• On MyISM tables this will result in a READ LOCK on the table.
• On InnoDB tables this will result in a WRITE LOCK on the table.

Analyzing data distribution in tables is relevant to both MyISM and InnoDB tables.

The equivalent to the above command is using:

myisamchk --analyze

…on MyISM tables and

mysqlcheck -Aa -uroot -p

or

mysqlcheck --analyze --all-databases<strong>

..on InnoDB tables.

One more thing, keep in mind that unlike myisamchk, mysqlcheck can run online while the database is open.