3

I have a table (InnoDB) with tens of millions of rows and several indexes. I need to delete a few million rows by primary key. This operation is rather slower than I'd like; what can I do to speed it up?

There are ongoing processes that do INSERTs and SELECTs on this table. If I can do it without interfering with those processes, that would be great, but it's not absolutely necessary. Also (I'm not sure if it makes a difference) there are no foreign keys in this DB.

Mike Baranczak
  • 8,291
  • 8
  • 47
  • 71
  • 1
    Show the `delete` code. – Yair Nevet Dec 07 '13 at 19:03
  • Since you will be using Primary key to delete the records(i.e you are referencing only Primary Key column in you where clause), it will be a covered query by the clustered index, it will be as fast as it can get, Deleting millions of rows will take its time. – M.Ali Dec 07 '13 at 19:07
  • This seems to be a pretty similar question: http://stackoverflow.com/questions/14284238/how-can-i-improve-delete-from-performance-on-large-innodb-tables – drew_w Dec 07 '13 at 19:08
  • One approach you can experience is breaking the statement `DELETE` in subparts (smaller chunks) using the [`split`](http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/query_script_split.html) statement of the framework [common_schema](https://code.google.com/p/common-schema/). – wchiquito Dec 07 '13 at 21:18

2 Answers2

1

You can use pt-archiver (Check out Percona toolkit) to delete records from a table non-blocking way.

pt-archiver --source h=host,D=db,t=child --purge --where 'id < 10000'

It will not be faster than the plain DELETE FROM db.child WHERE id < 10000, but at least it won't block other connections.

akuzminsky
  • 2,190
  • 15
  • 21
0

pt-archiver --no-check-charset --source h=localhost,D=ABCD,t=XYZ --purge --optimize=s --where "id < 10000"

Amelio
  • 11
  • 8