mysql - Deleting Rows from InnoDB is very slow

mysql sql innodb
user1938509 picture user1938509 · Apr 1, 2014 · Viewed 34.1k times · Source

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?

Answer

Uriil picture Uriil · Apr 1, 2014

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

Related questions

How to convert all tables from MyISAM into InnoDB?

I know I can issue an alter table individually to change the table storage from MyISAM to InnoDB. I am wondering if there is a way to quickly change all of them to InnoDB?

Read More
How to regex in a MySQL query

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I'm trying is this SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%') And SELECT trecord …

Read More
#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

So I am trying to add a primary key to one of the tables in my database. Right now it has a primary key like this: PRIMARY KEY (user_id, round_number) Where user_id is a foreign key. I …

Read More