Optimizing Your Table Structure

An optimized table structure is different from a well-designed table. Table structure optimization has to do with reclaiming unused space after deletions and basically cleaning up the table after structural modifications have been made. The OPTIMIZE TABLE SQL command takes care of this, using the following syntax:

OPTIMIZE TABLE table_name[,table_name]

For example, if you want to optimize the grocery_inventory table in the testDB database, use

mysql> optimize table grocery_inventory;
+--------------------------+----------+----------+----------+
| Table                    | Op       | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| testDB.grocery_inventory | optimize | status   | OK       |
+--------------------------+----------+----------+----------+
1 row in set (0.08 sec)

The output doesn't explicitly state what was fixed, but the text in the Msg_text column shows that the grocery_inventory table was indeed optimized. If you run the command again, the text will change, showing that it is a useful message:

mysql> optimize table grocery_inventory;
+-------------------------+----------+----------+-----------------------------+
| Table                   | Op       | Msg_type | Msg_text                    |
+-------------------------+----------+----------+-----------------------------+
| testDB.grocery_inventory| optimize | status   | Table is already up to date |
+-------------------------+----------+----------+-----------------------------+
1 row in set (0.03 sec)

Be aware that tables are locked while undergoing optimization, so if your table is large, perform the optimization during scheduled downtime or when little traffic is flowing to your system.