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?
Please feel free to use the comments form below if you have any questions or need more explanation on anything. I do not guarantee a response.
IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.
tags cloud
popular searches
free download for mysql database server 5.1.5, bison, gearman, source code, php, install cairo, laptop, mysql, java, linux, install mysql, mysql initialization, mysql mysql, tools, ubuntu
Similar Tutorials:
- Fast, parallel restore from SQL dumps (mysqldump) for MySQL
- MySQL error while dropping databases
- How to delete rows in table when PK is referenced in another table
- SQL: Union and Union All
- opensource-db-tools - A set of open source tools for MySQL
Tutorials in 'Database Management Systems > MySQL' (more):
- What should be monitored on a busy Database/MysQL server?
- How to speed up a Mysql replica?
- The server requested authentication method unknown to the client [phpmyadmin]
- How to set default values for MySQL JSON columns
- How to delete rows in table when PK is referenced in another table
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 J on Feb 27, 2012