if i have a list of Ids (1, 4,6,7) and a db table that i want to delete all records where id is in this list, what is the fastest way of doing this?
+11
A:
Your question almost spells the SQL for this:
DELETE FROM table WHERE id IN (1, 4, 6, 7)
Matti Virkkunen
2010-04-11 00:20:20
Has anyone benchmarked this vs one-by-one delete?
jayarjo
2010-06-08 10:59:05
@jayarjo: Any difference would probably be quite negligible, and I don't think there is any reason one-by-one delete would be any more efficient anyways.
Matti Virkkunen
2010-06-08 16:50:14
It's just where performance may hit convenience. For example I already got functions to do it one by one in place. But if there were a chance of rising performance I could write some additional code, otherwise probably it won't worth it.
jayarjo
2010-06-09 10:55:56
A:
The correct answer is that speed depends on the characteristics of the particular implementation/engine that you are using, and that you should beware of any quack who pretends to be able to give a definite answer to this question.
If the optimizer of your system is so poor that it does not spot the optimization opportunity from 'WHERE id IN (...)', then it will do a table scan, which might be a lot slower than giving 4 separate delete commands.
Erwin Smout
2010-04-11 09:35:57
Rubbish. Please cite one database engine where 4 separate deletes would be faster.
Blorgbeard
2010-04-11 09:47:07
If the optimizer in your system is so poor... you should switch to another database engine
Matti Virkkunen
2010-04-11 10:12:55