Defragment A Table

• Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages
  • If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented.
    • This can adversely affect performance.

• One way speed up index scans is to periodically perform a "null" ALTER TABLE operation.

  ALTER TABLE tbl_name ENGINE=INNODB

  • This causes MySQL to rebuild the table.

• Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

How is error handling done?

---

---

© 2007 John Michael Pierobon

Notes