MySQL - ANALYZE TABLE Statement

---

---

The MySQL ANALYZE statement analyzes the specified table. This statement has three syntaxes −

ANALYZE TABLE Statement Without HISTOGRAM

This statement stores the key distribution analysis of the specified table(s). Following is the syntax of this table −

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

Example

Assume we have created a table with name Players in MySQL database using CREATE statement as shown below −

CREATE TABLE Players(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in Players table using INSERT statements −

Insert into Players values
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'),
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'), 
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'),
(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'),
(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'),
(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following statement analyzes the above created table −

ANALYZE TABLE Players;

Output

Following is the output of the above program −

Table	Op	Msg_type	Msg_text
mydb.players	analyze	status	OK

ANALYZE TABLE Statement with UPDATE HISTOGRAM

This statement stores the histogram statistics for the desired columns of the specified table and stores them in the data dictionary. Following is the syntax of this table −

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

Example

Following query analyzes the above created Players table using the ANALYZE TABLE Statement with UPDATE HISTOGRAM.

ANALYZE TABLE Players UPDATE HISTOGRAM ON First_Name, Last_Name;

Output

The above mysql query produces the following output −

Table	Op	Msg_type	Msg_text
mydb.players	histogram	status	Histogram statistics created for column 'First_Name'.
mydb.players	histogram	status	Histogram statistics created for column 'Last_Name'.

ANALYZE TABLE Statement with DROP HISTOGRAM

This statement removes the histogram statistics for the columns of the specified table and from the data dictionary. Following is the syntax of this table −

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

Example

Following query is an example of the ANALYZE TABLE Statement with DROP HISTOGRAM −

ANALYZE TABLE Players DROP HISTOGRAM ON First_Name, Last_Name;

Output

Following is the output of the above mysql query −

Table	Op	Msg_type	Msg_text
mydb.players	histogram	status	Histogram statistics removed for column 'First_Name'.
mydb.players	histogram	status	Histogram statistics removed for column 'Last_Name'.

Analyzing multiple tables

You can also analyze multiple tables and get the results using the ANALYZE TABLE Statement.

Example

Assume we have created three new tables as shown below −

CREATE TABLE Test1(ID INT, Name VARCHAR(255));
CREATE TABLE Test2(ID INT, Name VARCHAR(255));
CREATE TABLE Test3(ID INT, Name VARCHAR(255));

Following query analyzes all these tables and displays the result −

ANALYZE TABLE Test1, Test2, Test3;

Output

The above mysql query will produce the following output −

Table	Op	Msg_type	Msg_text
mydb.test1	analyze	status	OK
mydb.test2	analyze	status	OK
mydb.test3	analyze	status	OK

ANALYZE statement with EXPLAIN

If used with ANALYZE the EXPLAIN statement gives additional information such as timing of the execution and iterator-based, information like −

• Estimated execution cost.
• Estimated number of returned rows.
• Time to return first row.
• Time to return all rows (actual cost), in milliseconds.
• Number of rows returned by the iterator.
• Number of loops.

Example

Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

Now, let us try to insert some records into the EMPLOYEE table −

INSERT INTO Employee VALUES
(101, 'Ramya', 'Rama Priya', 27, 'F', 9000, 101),
(102, 'Vinay', 'Bhattacharya', 20, 'M', 6000, 102);

And, if we have created another table and populated it as −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

Let's try to insert some records into the CONTACT table using the INSERT statement −

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, 'ramya@mymail.com', 'Hyderabad'),
(102, 'vinay@mymail.com', 'Vishakhapatnam');

Following query deletes records from the above created tables −

SELECT * FROM EMPLOYEE INNER JOIN CONTACT ON 
CONTACT.id = EMPLOYEE.id\G;

Output

Following is the output of the above query −

************ 1. row ************
        ID: 101
FIRST_NAME: Ramya
 LAST_NAME: Rama Priya
       AGE: 27
       SEX: F
    INCOME: 9000
   CONTACT: 101
        ID: 101
     EMAIL: ramya@mymail.com
     PHONE: NULL
      CITY: Hyderabad
************ 2. row ************
        ID: 102
FIRST_NAME: Vinay
 LAST_NAME: Bhattacharya
       AGE: 20
       SEX: M
    INCOME: 6000
   CONTACT: 102
        ID: 102
     EMAIL: vinay@mymail.com
     PHONE: NULL
      CITY: Vishakhapatnam

Following EXPLAIN statement displays the information about the above query −

EXPLAIN ANALYZE SELECT * FROM EMPLOYEE INNER JOIN CONTACT ON 
CONTACT.id = EMPLOYEE.id\G;
EXPLAIN ANALYZE SELECT * FROM EMPLOYEE INNER JOIN CONTACT ON 
CONTACT.id = EMPLOYEE.id\G;

Ouptut

Following is the output −

*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (contact.ID = employee.ID) 
(cost=1.10 rows=2) (actual time=0.086..0.091 rows=2 loops=1)
   -> Table scan on CONTACT (cost=0.18 rows=2) 
   (actual time=0.008..0.011 rows=2 loops=1)
   -> Hash
      -> Table scan on EMPLOYEE (cost=0.45 rows=2) 
	  (actual time=0.043..0.051 rows=2 loops=1)

1 row in set (0.00 sec)