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;
Did this tutorial help a little? How about buy me a cup of coffee?
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.