0

I have a myisam database running on MariaDB 10.x. I'm pretty sure it has a lot of whitespace that I'd like to trim down. I was planning to run OPTIMIZE <TABLE NAME>; but a thought occurred to me: would it be faster/easier to just collect a backup and restore it?

For example...

1) Run: mysqldump -u root --hex-blob --add-drop-database --databases FooDatabase > /tno/FooBackup.sql

2) Then run: mysql -u root -vvv < /tmp/FooBackup.sql

The white space wouldn't be in the backup file so presumably it would be compact upon restoring... and that way, MariaDB wouldn't need to go row by row checking for stuff.

Is that a bad idea? Admittedly, the backup/restore wouldn't check for errors or do the other things that optimize does but if I'm ONLY interested in eliminating white space, wouldn't it be faster?

Improve this question
3
  • "white space"? That is not what gets removed via OPTIMIZE. That command is rarely needed, even for MyISAM.
    – Rick James
    Jul 13, 2017 at 4:56
  • @RickJames Sorry - I'm probably misusing the term. I mean shrinking the physical size of the file by removing empty space within the table. As per mariadb.com/kb/en/mariadb/optimize-table "You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. "
    – Mike B
    Jul 13, 2017 at 5:26
  • SHOW TABLE STATUS -- if Data_free is less than 10% of the total, don't bother doing OPTIMIZE.
    – Rick James
    Jul 13, 2017 at 6:02

1 Answer 1

Reset to default
1

Both will achieve essentially the same thing. If your exact db version doesn't offer online OPTIMIZE then the dump and restore could be useful if you restore into a new table and then DROP the old one and RENAME the new one, allowing concurrent selects during the import. Regarding the speed, the dump and restore might be a bit faster but it's probably not a huge gain. I prefer it if I'm working with a version that doesn't support online OPTIMIZE.

As Rick James mentioned this sort of thing isn't often necessary, but I have seen it help to bulk re-balance indices on tables with columns struggling to keep up with a large number of concurrent updates.

Improve this answer
2
  • Just to clarify - I'm using MyISAM. I don't think that changes much about your answer but thought I'd reiterate that detail.
    – Mike B
    Jul 17, 2017 at 14:00
  • Ah yes, no it doesn't change much except for that the innodb parameters for a faster import become irrelevant.
    – MarCPlusPlus
    Jul 17, 2017 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.