GNU Info

(mysql.info)InnoDB File Defragmenting

---

Prev: InnoDB File space Up: File space management

---

Enter node , (file) or (file)node

Defragmenting a table
.....................

If there are random insertions or deletions in the indexes of a table,
the indexes may become fragmented. By fragmentation we mean that the
physical ordering of the index pages on the disk is not close to the
alphabetical ordering of the records on the pages, or that there are
many unused pages in the 64-page blocks which were allocated to the
index.

It can speed up index scans if you periodically use `mysqldump' to dump
the table to a text file, drop the table, and reload it from the dump.
Another way to do the defragmenting is to `ALTER' the table type to
`MyISAM' and back to `InnoDB' again.  Note that a `MyISAM' table must
fit in a single file on your operating system.

If the insertions to and index are always ascending and records are
deleted only from the end, then the the file space management algorithm
of InnoDB guarantees that fragmentation in the index will not occur.