Announcement Module
No announcement yet.

deleting millions of rows

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • deleting millions of rows

    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.