MySQL Optimize Table Command

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

There is a table called articles which has many thousands of rows which are often inserted, updated and deleted. 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)

The size of the table on disk is about 190MB. Query the table on a column that is indexed to see the average query response time:

mysql> select count(*) from articles where article_title like 'The%';

+----------+
| count(*) |
+----------+
|    15830 |
+----------+

1 row in set (0.63 sec)

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)

The optimization has the effect of defragmenting the table and reducing the size of the table on disk down to 105 MB. It also has a very positive effect on query performance, reducing the select query response time from 0.63 to 0.39 seconds.

Note: The MySqQL 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)