I’m working with MySQL 5.7 on Windows.
I’ve got several tables to clean data from. Largest table’s ibd file is 300GB in size and it has almost 1.5 billion lines. I need to leave about 290 million rows in it. So a very large chunk needs to be removed.
From reading the docs there are 2 ways
- DELETE statement and OPTIMIZE TABLE after that
- copying data to a new table, dropping the old one and renaming the new one.
Second option seems much better in this case, but it there any potential issues to look our for? Downtime is not really an issue.
Another question, I saw that delete statement on big tables (deleting 1 million+ rows) can get stuck and then cause ibdata to grow (hundreds of MB), even though innodb_file_per_table option is on.
I assume that has to do with temporary tables somehow, but can’t find an explanation. Any ideas?