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?

Does this answer your question? [Delete with Join in MySQL](https://stackoverflow.com/questions/652770/delete-with-join-in-mysql) — Daniel Brose, May 28 '20 at 22:21
just add a WHERE t2.points = 0 or similar to the joined delete query from the link above — Daniel Brose, May 28 '20 at 22:22
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, May 28 '20 at 22:39
Do it in chunks to avoid timeouts and locking: http://mysql.rjweb.org/doc.php/deletebig — Rick James, May 31 '20 at 02:44

GMB · Accepted Answer · 2020-05-28T22:56:56.023

3

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 '20 at 22:56

answered May 28 '20 at 22:20

GMB

216,147
25
84
135

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. – Natsu May 28 '20 at 22:53
1

@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. – GMB May 28 '20 at 22:57
1

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! – Natsu May 28 '20 at 23:07

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

1 Answers1