Massive sql delete query

Question

I have to delete a lot of data based on id value. Which one is the fastest :

DELETE FROM myTable WHERE id = '10' OR id = '20' OR id = '43' OR id = '54' .......

or

DELETE FROM myTable WHERE id = '10'; DELETE FROM myTable WHERE id = '20'; ........

If you have an another solution, don't hesitate to tell me.

(the id here is not primary key or indexed, is a foreignkey because it references a word and I can have multiple entry for one word in this database)

I use MySQL but I want that my query can be applied on other RDBMS

The first one is better, because there will be less logging-related work to do by database engine. — MarcinJuraszek, Commented Apr 7, 2013 at 9:57
thanks because the two way take very too much time. So hard to measure it. What do you think about Sachin example ? I use MySQL but I want my query to be applied on other RDBMS too — Dahevos, Commented Apr 7, 2013 at 10:02
quite like the question title :). will add to the curiosity of whats massive inside. — Praveen Nambiar, Commented Apr 7, 2013 at 10:20

Sachin · Accepted Answer · 2013-04-07 10:11:49Z

5

The first one is faster but I would like to use IN operator like this for more readability though with same performance

DELETE FROM myTable WHERE id in ('10','20','30',...)

answered Apr 7, 2013 at 9:58

Sachin

40.8k7 gold badges91 silver badges105 bronze badges

Don't forget the inverted commas. It seems the id's data type is text/nvarchar.
– Jerry
Commented Apr 7, 2013 at 10:11

Add a comment |

Muhammad Omar ElShourbagy · Accepted Answer · 2013-04-07 11:28:05Z

0

First one should be faster,

Having one statement is better than having set of statements in terms of Query Optimizer output.

answered Apr 7, 2013 at 10:03

6,9952 gold badges33 silver badges50 bronze badges

1

The second solution does not necessarily mean there are multiple transactions. A transaction can span more than one statement. But I do agree that a single statement is most probably faster than multiple statements.
– user330315
Commented Apr 7, 2013 at 10:17
judging by your reputation, you should be aware that SQL can run a statement without transactions. i'm pretty sure of that on MS SQL Server not only MySQL !
– Muhammad Omar ElShourbagy
Commented Apr 7, 2013 at 11:39
No you can not, not even on SQL Server. You might have an implicit transaction that is automatically maintained by the engine, but it's impossible to run a SQL statement without a transaction (again with the MySQL exception of not supporting transactions at all)
– user330315
Commented Apr 7, 2013 at 12:26

Add a comment |

2 Answers 2