IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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.

New 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.

New 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
New 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.

New 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

New But won't I just be spending the time ...
inserting the elements into the other table. This is a one time event.
New 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.
     Bulk delete - (bluke) - (6)
         Partition the DELETE. - (static) - (2)
             MySQL != MS-SQL - (altmann) - (1)
                 D'oh. Missed that. Sorry. -NT - (static)
         Just throw the list in another table - (ChrisR) - (2)
             But won't I just be spending the time ... - (bluke) - (1)
                 If it's a one time event... - (ChrisR)

Drop to the deck and flop like a fish!
60 ms