Search code examples
mysqlsqlinnodb

mysql - Deleting Rows from InnoDB is very slow

I got a mysql database with approx. 1 TB of data. Table fuelinjection_stroke has apprx. 1.000.000.000 rows. DBID is the primary key that is automatically incremented by one with each insert.

I am trying to delete the first 1.000.000 rows using a very simple statement:

Delete from fuelinjection_stroke where DBID < 1000000;

This query is takeing very long (>24h) on my dedicated 8core Xeon Server (32 GB Memory, SAS Storage).

Any idea whether the process can be sped up?

Solution

  • I believe that you table becomes locked. I've faced same problem and find out that can delete 10k records pretty fast. So you might want to write simple script/program which will delete records by chunks.

       DELETE FROM fuelinjection_stroke WHERE DBID < 1000000 LIMIT 10000;

    And keep executing it until it deletes everything