MySQL Performance Optimization Part I

If you have deleted a large no. of records from a table or if you have made many changes to a table with variables length rows, you should run OPTIMIZE TABLE command to reclaim the unused space and defragment the table file.

OPTIMIZE TABLE, in almost all cases, improves performance. However, keep in mind that running OPTIMIZE TABLE on a table locks the table.

OPTIMIZE TABLE table_name;

Replace table_name with the name of the table you want to optimize. Also, if you want run OPTIMIZE TABLE on a replication master then the command will be replicated to all slaves causing the tables to be locked in slaves too. In order to avoid this, you can run OPTIMIZE TABLE as follows:

OPTIMIZE LOCAL TABLE table_name;

or

OPTIMZE NO_WRITE_TO_BINLOG TABLE table_name;

For more info, see the relevant documentation on MySQL website: MySQL 4.1.x, MySQL 5.0.x, and MySQL 5.1.x.

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.

Comments (write a comment):

The DELETE, itself, could be a problem. Have you every watched the system when you delete a million rows?

InnoDB needs to keep track of every row being deleted, in case it needs to do a ROLLBACK (which it will do if the system crashes).

Here is a discussion of various ways of not hampering the system when you need to do a big DELETE:
http://mysql.rjweb.org/doc.php/deletebig

The OPTIMIZE is beneficial for MyISAM after a huge DELETE. It is less important for InnoDB.

For neither engine will the DELETE give space back to the OS. Posted by: Rick James on Feb 27, 2012

leave a comment on tutorial leave a comment