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.

Please feel free to use the comments form below if you have any questions or need more explanation on anything.

Tags

mysql, performance, optimization, optimize,

Popular Searches

linux, php, mysql, ubuntu, mysql mysql, tools, install mysql, gearman, source code, java

more>>

Comments (write a comment):

0 comments so far. Be the first one to leave a comment on this article.

 

Comment
 

 
| Members | Support Us | Using this site means you accept its terms | Contact Us | Site Map |