SQL Most efficient way to delete with 150000 'OR' statements

Question

We hold a DB of ~350k email addresses for a client.

They have supplied me with a couple of excel spreadsheets with a combined ~150k email addresses they want to remove (long story short, it was a spammy email list that they had gotten hold of and want to remove the bounce backs).

Using the wonders of find and replace, I have got a query built to perform this huge delete but the query takes too long and times out (no surprise there).

The query I have built is in the form of:

DELETE FROM emaillist
WHERE intListID = 68
AND
(strEmailAddress = "aaaa@aaaa.com"
OR strEmailAddress = "aaab@aaaa.com"
OR strEmailAddress = "aaac@aaaa.com"
......
etc etc etc
......
OR strEmailAddress = "zzzy@zzzz.com"
OR strEmailAddress = "zzzz@zzzz.com"
)

Is there a better way to do this?

y don't u store this email in a temp table and run the delete query? — Jianhong, Sep 03 '13 at 14:30
If you can insert them in a table or a temp table, then you can `DELETE` with `JOIN`: `DELETE t1 FROM emaillist INNER JOIN spammyemail AS t2 ON t1.strEmailAddress = t2.emailaddress` — Mahmoud Gamal, Sep 03 '13 at 14:30

score 10 · Accepted Answer · answered Sep 03 '13 at 14:31

10

Put the emails you want to delete in a temporary table. Then you can remove the emails from your original table by using a join

DELETE e 
FROM emaillist e
INNER JOIN temp_table t ON t.strEmailAddress = e.strEmailAddress
WHERE intListID = 68

After that drop the temp table and your done.

answered Sep 03 '13 at 14:31

juergen d

201,996
37
293
362

@Fraser After that, we're interested in your query execution time ;-) – Jan Doggen Sep 03 '13 at 14:42
Running it now. Will report back :) – Fraser Sep 03 '13 at 14:44
1

Affected rows: 124740 Time: 70.278ms – Fraser Sep 03 '13 at 14:47

score 2 · Answer 2 · answered Sep 03 '13 at 14:33

I would suggest the following steps:

Load the Excel emails into a "todelete"
Build an index on the email field in the "todelete" table
Create a new table with the values you want:

create table newemails as select * from emaillist where not exists (select 1 from todelete where todelete.email = emaillist.email);
Truncate emaillist
Insert the list back in

The said, you might find that steps (1) and (2) are sufficient, with the query:

delete from emaillist
    where not exists (select 1 from todelete where todelete.email = emaillist.email);

However, deleting 150,000 rows generates a lot of traffic on the log. Truncation and re-insertion saves this overhead.

score 0 · Answer 3 · answered Sep 03 '13 at 14:34

You could import the list in a temporary table and try something like:

DELETE FROM emaillist
WHERE intListID = 68
AND strEmailAddress in (Select emailAddress From temporaryTable)

An index on strEmailAddress (even created for the duration of the query) could help a lot.

SQL Most efficient way to delete with 150000 'OR' statements

3 Answers3