Friday, July 17, 2009

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:

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.