OPTIMIZE is probably better because what it does is
CREATE TABLE ...
- Copy all the existing rows over
(Some time during or after step 2, the indexes are rebuilt.)
Dump and reload:
- Read the entire table, write to disk
DROP TABLE or
TRUNCATE TABLE and
CREATE TABLE ...
- Read the dump, inserting into the table
This is slower because of the extra I/O for the dump.
If disk space is tight, execute the dump (mysqldum/xtrabackup/etc) and restore (mysql) from a remote machine and have the dump file on that remote machine. This may be the only way to do the cleanup without running out of disk space.
Next time you need to do a big
CREATE TABLE new LIKE real
- Copy the rows you do not want to delete into
RENAME TABLE real TO old, new TO real
DROP TABLE old
This 'eliminates' the effort to do the
Deleting big chunks is a popular topic; see my blog on the topic.
Still even better...
If this is a time series, and you are dropping "old" data,
PARTITION on the date. Then
DROP PARTITION to jettison the old data 'instantly'. Details.
If you have only 20GB of spare disk, and the remaining data + index in the InnoDB table is more than 20GB,
OPTIMIZE will run out of disk space and fail (without doing 'anything'). So will dump+reload unless you can use a different machine for the dump. (The 'better' solutions need setup; can't do either this time.)
If the table is in
innodb_file_per_table), no space will be given back to the OS. It will be available for future growth in InnoDB tables.
Their may be a difference is size or speed between the restore and the
OPTIMIZE. The rebuilding of the indexes may be different.
If you are doing any of this to recoup space on disk, start with the smallest table first and work your way up. Keep in mind that InnoDB makes some attempt at cleaning up holes in its BTrees. In theory, a well-kept BTree will average 31% empty space, even after lots of churn. So, that's the most you can expect to recoup from
OPTIMIZE (or equivalent). However, a huge delete leaves free space in addition to the 31%.