MySQL ANALYZE TABLE Statement

Summary: in this tutorial, you will learn how to use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics.

Introduction to MySQL ANALYZE TABLE statement

In MySQL, the query optimizer relies on table statistics to optimize query execution plans.

The table statistics help the query optimizer estimate the number of rows in a table that satisfy a particular condition.

However, sometimes the table statistics can be inaccurate. For example, after you have done a lot of data changes in the table such as inserting, deleting, or updating.

If the table statistics are not accurate, the query optimizer may pick a non-optimal query execution plan that may cause a severe performance issue.

To address this issue, MySQL provides the ANALYZE TABLE statement that updates these statistics, ensuring that the query optimizer has accurate information for efficient query planning.

The following ANALYZE TABLE statement performs a key distribution analysis:

ANALYZE TABLE table_name [, table_name];Code language: SQL (Structured Query Language) (sql)

In this syntax:

table_name: The name of the table that you want to analyze. If you want to analyze multiple tables, you separate them by commas.

This key distribution analysis is essential for understanding the distribution of key values within the table. The query optimizer uses the results of this statement to optimize join operations and index usage.

The ANANLYZE TABLE statement works only with InnoDB, NDB, and MyISAM tables.

MySQL ANALYZE TABLE statement example

We’ll use the table from the sample database for the demonstration.

First, log in to the MySQL Server using the root account:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

It’ll prompt you to enter a password for the root account.

Second, switch the current database to classicmodels:

use classicmodels;Code language: SQL (Structured Query Language) (sql)

Third, analyze the customers table:

analyze table customers;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+---------+----------+----------+
| Table                   | Op      | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| classicmodels.customers | analyze | status   | OK       |
+-------------------------+---------+----------+----------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The output table has the following columns:

Table: The table name that was analyzed.
op: analyze or histogram.
Msg_type: show the message type including status, error, info, note, or warning.
Msg_text: an informational message.

Summary

Use the MySQL ANALYZE TABLE statement to ensure that the query optimizer has accurate and up-to-date table statistics, allowing it to generate optimal query execution plans.

Was this tutorial helpful?