MySQL reclaim index space after large delete?

After performing a large delete in MySQL, I understand you need to run a NULL ALTER to reclaim disk space, is this also true for reclaiming index space?

We have tables using > 10G of index space and have deleted/archived large chunks of this data and unsure if we need to rebuild the table in order to decrease the size of the index.

Can anyone offer any advice?

We are trying to avoid rebuilding the table since it would take quite awhile and lock the table.

Thanks!


You're using InnoDB. Unfortunately, in its default configuration, InnoDB never gives back disk space unless you are using innodb_file_per_table, in which case disk space can be re-claimed by running optimize table foo.

If you are not using innodb_file_per_table, then you need to do the following:

  1. Use mysqldump to dump your databases to a file.
  2. Drop your databases.
  3. Stop mysql.
  4. Delete your innodb files (or move them elsewhere).
  5. Start up mysql.
  6. Re-create your databases and re-import from the mysqldump file.

Needless to say, you had better make sure you have a very good backup before doing this. Also, strongly consider enabling innodb_file_per_table while you have mysql stopped.