top of page
CerebroSQL

MySQL: 

ANALYZE TABLE

Syntax:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name
UPDATE HISTOGRAM ON col_name [, col_name] ...
[WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name
DROP HISTOGRAM ON col_name [, col_name] ...

ANALYZE TABLE generates table statistics:

o ANALYZE TABLE without either HISTOGRAM clause performs a key
distribution analysis and stores the distribution for the named table
or tables. For MyISAM tables, ANALYZE TABLE for key distribution
analysis is equivalent to using myisamchk --analyze.

o ANALYZE TABLE with the UPDATE HISTOGRAM clause generates histogram
statistics for the named table columns and stores them in the data
dictionary. Only one table name is permitted for this syntax.

o ANALYZE TABLE with the DROP HISTOGRAM clause removes histogram
statistics for the named table columns from the data dictionary. Only
one table name is permitted for this syntax.

This statement requires SELECT and INSERT privileges for the table.

ANALYZE TABLE works with InnoDB, NDB, and MyISAM tables. It does not
work with views.

If the innodb_read_only system variable is enabled, ANALYZE TABLE may
fail because it cannot update statistics tables in the data dictionary,
which use InnoDB. For ANALYZE TABLE operations that update the key
distribution, failure may occur even if the operation updates the table
itself (for example, if it is a MyISAM table). To obtain the updated
distribution statistics, set information_schema_stats_expiry=0.

ANALYZE TABLE is supported for partitioned tables, and you can use
ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions;
for more information, see [HELP ALTER TABLE], and
https://dev.mysql.com/doc/refman/8.0/en/partitioning-maintenance.html.

During the analysis, the table is locked with a read lock for InnoDB
and MyISAM.

ANALYZE TABLE removes the table from the table definition cache, which
requires a flush lock. If there are long running statements or
transactions still using the table, subsequent statements and
transactions must wait for those operations to finish before the flush
lock is released. Because ANALYZE TABLE itself typically finishes
quickly, it may not be apparent that delayed transactions or statements
involving the same table are due to the remaining flush lock.

By default, the server writes ANALYZE TABLE statements to the binary
log so that they replicate to replicas. To suppress logging, specify
the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

URL: https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

Example

bottom of page