deleting millions of rows

MySQL & MariaDB Other MySQL® Questions
1

I have a MyASM table with 70 million rows.

I want to delete most of those rows using an index column.

I’m running a DELETE QUICK command at the moment:

DELETE QUICK FROM table_name WHERE colum_id IN (SELECT DISTINCT column_id FROM another_table WHERE blah = 1)

after the deletion is finished, I plan running OPTIMIZE TABLE

So – I’ve really got two questions:

  1. Is there any way to see how many rows it’s already deleted?
  2. Is there any faster way to do this? Should I CTRL-C the current command, then run OPTIMIZE TABLE, then run another delete?
2

It might be quicker to save the rows you want to keep in a new table, then do a TRUNCATE TABLE on the old table, then copy the saved rows back to it.