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

Question

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?

just add a WHERE t2.points = 0 or similar to the joined delete query from the link above — Daniel Brose
– Daniel Brose, Commented May 28, 2020 at 22:22
Do it in chunks to avoid timeouts and locking: mysql.rjweb.org/doc.php/deletebig — Rick James
– Rick James, Commented May 31, 2020 at 2:44

GMB · Accepted Answer · 2020-05-28 22:56:56Z

4

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;

edited May 28, 2020 at 22:56

answered May 28, 2020 at 22:20

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Natsu Over a year ago

It's not a huge amount of information. On a backup replication server, I tried the first query and got this error: " #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'u where not exists (select 1 from points p where p.user_id = u.id)' at line 1 ". I'm not sure ewhat the select 1 really does.

GMB Over a year ago

@Natsu: it seems like MySQL did not support table aliases in the delete statement prior to version 8.0. I updated the first query in my answer, please give it a try.

Natsu Over a year ago

Thank you! That is so much better, I tried something similiar when your original one didn't work, but I was still a bit off. I'm so split between SQL Server and MySQL I get confused sometimes. I appreciate it!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related