MySQL Optimize Table

The MySQL Optimize Table command will effectively de-fragment a mysql table and is very useful for tables which are frequently updated and/or deleted.

This statement requires SELECT and INSERT privileges for the table.

OPTIMIZE TABLE works only for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. It does not work for tables created using any other storage engine.

For MyISAM tables, OPTIMIZE TABLE works as follows:

1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
   
   

Example:

We have a table called articles which has many thousands of rows which are often inserted, updated and deleted. We can see from the table description below that the table contains variable length column data types:

mysql> desc articles;

+----------------+--------------+------+-----+---------+----------------+

| Field  | Type | Null | Key | Default | Extra |

+----------------+--------------+------+-----+---------+----------------+

| id  | int(11) | NO   | PRI | NULL    | auto_increment |

| content        | text         | NO   |     | NULL    |                |

| author_id      | int(11)      | YES  |     | NULL    |                |

| article_title  | varchar(120) | YES  |     | NULL    |                |

| article_hash   | int(11)      | YES  |     | NULL    |                |

+----------------+--------------+------+-----+---------+----------------+

6 rows in set (0.00 sec)

If we look at the size of the table on disk we can see that it around 190MB. If we query the table on a column which is indexes we can see the average query response time: 

e.g.

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.63 sec)

If we now optimize the table with the following command:

mysql> optimize table articles;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| books.articles        | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (6.27 sec)

This has the effect of defragmenting the table and reducing the size of the table on disk down to 105MB. It also has a very positive affect on query performance, reducing the select query response time from 0.63 to 0.39 seconds. N.B. the mysql query cache was turned off to demonstrate.

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.39 sec)