- Building an Optimized Platform
- MySQL Startup Options
- Optimizing Your Table Structure
- Optimizing Your Queries
- Summary
- Q&A
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Optimizing Your Table Structure
An optimized table structure is different than 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 SQL command takes care of this, using the following syntax:
OPTIMIZE TABLE table_name[,table_name]
For example, if you want to optimize the master_name table in your contact management database, use:
mysql> OPTIMIZE TABLE master_name; +-----------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+----------+----------+----------+ | contactDB.master_name | 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 master_name table was indeed optimized. If you run the command again, the text will change, showing that it is a useful message:
mysql> OPTIMIZE TABLE master_name; +-----------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------+----------+----------+-----------------------------+ | contactDB.master_name | optimize | status | Table is already up to date | +-----------------------+----------+----------+-----------------------------+ 1 row in set (0.03 sec)
Be aware that the table is locked while it is optimized, so if your table is large, optimize it during scheduled downtime or when little traffic is flowing to your system.
NOTE
You can use OPTIMIZE on only MyISAM and BDB tables.