Bulk delete

Post #171,625

8/30/04 2:21:16 AM

Bulk delete

We have an application where the user can multiple select items (could be large numbers like a 1000 items) to delete. The way we thought of implementing this is as follows:
delete from tablename where id in (id1, id2,...)

where we get the id's from the items that the user selected. The dba is not happy with this, he claims a big in clause kills performance of the database. In fact, he said, in MS-SQL you are limited to 500 items in an in clause.

Any suggestions on some other way to do this? The data is in 1 table and we are deleting only the selected records.

Post #171,628

by static

8/30/04 3:16:35 AM

Partition the DELETE.

That is, construct DELETE statements 100 or 200 ids at a time. You might also be able to do a DELETE LOW_PRIORITY which makes sure no reads are pending when the delete happens.

The MySQL manual says the size of IN is limited only by the maximum packet size. And that if they're all constants of the same type as the comparing expression, it sorts them and uses a binary search. I think your DBA is talking through his hat.

Wade.

Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed

Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please

-- "Anything but Ordinary" by Avril Lavigne.

Post #171,630

by altmann

8/30/04 4:10:28 AM

MySQL != MS-SQL

But you could use that method to break the deletes up into chunks. Or you could add the selected IDs to a temp table and use that table in your in clause or do an inner join on it. I don't know which would perform best in your situation.

--
Chris Altmann

Post #171,798

by static

8/30/04 9:53:08 PM

D'oh. Missed that. Sorry.

Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed

Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please

-- "Anything but Ordinary" by Avril Lavigne.

Post #171,659

by ChrisR

8/30/04 10:39:32 AM

Just throw the list in another table

And either do an IN clause or a direct join:

\n   DELETE MyTable\n   FROM\n      MyTable myt,\n      DelTable dlt\n   WHERE (myt.key = dlt.key)\n

Post #171,660

by bluke

8/30/04 10:41:15 AM

But won't I just be spending the time ...

inserting the elements into the other table. This is a one time event.

Post #171,663

by ChrisR

8/30/04 10:55:15 AM

If it's a one time event...

...i don't think it matters which way you do it.

IN clauses are fairly efficient if the column is indexed. Must admit that I've never heard of the 500 limit on the items. Just tried an IN clause with 1028 entries and it worked fine. Think your DBA is thinking of 6.5 days.

Welcome to IWETHEY!