The MySQL Optimize Table Command

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.

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)

Source: dbtuna.com