CODEWITHSUNDEEP
mysqlsqljoinquery-optimizationsql-delete
Natsu
Natsu

Reputation: 131

Efficiently deleting rows from one table where not matching another [MySQL]

So, I have two tables, users and points. A lot of users no longer have points (literally and by table) -- so I wish to delete them. I try and achieve this by doing this, but it basically times out (about one hundred thousand rows in both tables). I'm doing select rather than delete because I want to verify the data before issuing the same query as a delete:

SELECT WWW t1 
FROM users t1
JOIN points t2 ON t1.id != t2.user_id;

Both tables have an ID that matches each other. If a user has a point, there will be a matching ID. If there is no longer a point for that user, there is no matching ID. Therefor, != therorhetically should delete all the no longer needed users.

How can I do this without timing out my database?

Upvotes: 2

Views: 910

Answers (1)

GMB
GMB

Reputation: 222582

I would recommend not exists:

delete from users
where not exists (select 1 from points p where p.user_id = users.id)

This should be an efficient option, if you have the right index in place, that is: points(user_id).

On the other hand, if you are deleting a great proportion of the table, it might be simpler to move that rows that you want to keep to a temporary table, then truncate and recreate the table:

create table tmp_users as 
select u.*
from users
where exists(select 1 from points p where p.user_id = u.id)

truncate table users;  -- back it up first!

insert into users select * from tmp_users;

drop table tmp_users;

Upvotes: 4

Related Questions