MySQL - OPTIMIZE TABLE Statement

---

---

When we perform several changes on an existing table (like insert, update, delete etc.) the physical storage of the table changes resulting performance degradation.

MySQL OPTIMIZE TABLE Statement

The MySQL OPTIMIZE table helps you to optimize the table storage space. It reorganizes the storage data in a way that increases the Input Output efficiency and reduces the storage space. To execute this statement, you need SELECT and INSERT privileges.

Syntax

Following is the syntax of the MySQL OPTIMIZE TABLE Statement −

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

Example

Assume we have created a table named EMPLOYEE with the CREATE statement as shown below −

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

And if we have inserted 4 records in to it using INSERT statements as −

INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);

Now, let us increase the age of all male employees by one year −

UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M';
Rows matched: 3 Changed: 3 Warnings: 0

And, following statement deletes records from the above table −

DELETE FROM EMPLOYEE WHERE FIRST_NAME = 'Mac';

Following query optimizes the above created table −

OPTIMIZE TABLE EMPLOYEE\G;

Output

The above query produces the following output −

************ 1. row ************
   Table: mydb.employee
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (2.74 sec)

Analyzing multiple tables

You can also optimize multiple tables and get the results using the OPTIMIZE 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 optimizes all these tables and displays the result −

OPTIMIZE TABLE Test1, Test2, Test3\G;

Output

Following is the output of the above query −

********** 1. row **********
   Table: mydb.test1
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
********** 2. row **********
   Table: mydb.test1
      Op: optimize
Msg_type: status
Msg_text: OK
********** 3. row **********
   Table: mydb.test2
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
********** 4. row **********
   Table: mydb.test2
      Op: optimize
Msg_type: status
Msg_text: OK
********** 5. row **********
   Table: mydb.test3
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
********** 6. row **********
   Table: mydb.test3
      Op: optimize
Msg_type: status
Msg_text: OK
6 rows in set (4.52 sec)