How ANALYZE TABLE statement helps in maintaining the MySQL tables?

MySQLMySQLi Database

MySQL query optimizer is an important element of the MySQL server that makes an best question execution set up for a query. For a particular query, the query optimizer uses the stored key distribution and other factors to decide the order in which tables should be joined when you performing the join, and which index should be used for a specific table.

However, the key distributions can be sometimes inaccurate e.g., after you have done a lot of data changes in the table including insert, delete, or update. IIf the key distribution isn't correct, the question optimizer could pick a nasty query execution arrange that will cause a severe performance issue.

ANALYZE TABLE statement can solve the above problem. We need to run the ANALYZE TABLE statement as follows −

ANALYZE TABLE table_name

Here, table_name is the name of the table.


We are running this statement for the table Student_info as follows −

mysql> Analyze table student_info\G;
*************************** 1. row ***************************
   Table: query.student_info
      Op: analyze
Msg_type: status
Msg_text: OK

1 row in set (0.22 sec)
Published on 20-Feb-2018 13:41:37