Web Technologies

MySQL Table optimization

April 24th, 2013 at 11:04

Sizable insert, update or delete actions may result in unused space in tables. To counter that we use need to optimize tables in MySQL database. MySQL usually manages table data pretty decently.

What table optimization does?

Table optimization does following tasks.

  • Defragmentation of table to purge wasted space and reduces the size of table.
  • Merges the content of different length rows that have become fragmented into non-contiguous parts to store each row contiguously.
  • Sort the index page if needed.
  • Update Metadata of database.
  • Once table is optimized we can find improvement in performance of database.

Disadvantages

  • It is not easy to decide when to optimize in busy environment as optimization should be done in off peak time.
  • Running optimization can affect database performance for the time taken by process as it locks the table for any modification or updates.

How often optimization should be done in MySQL?

With Innodb tables, some level of fragmentation is expected and normal. You will never remove all free space. Once in 6 months in a busy environment can be handy otherwise for different level of database it could vary.

Alternative

An alternative command has been used in previous versions is myisamchk with –quick and –analyze options.

Comments are currently not open for this post.