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

Natsu :

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?

GMB :

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;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL: Update rows in table, from rows with matching key in another table

From Dev

How to delete rows from one table matching another table?

From Dev

mysql - move rows from one table to another

From Dev

MySQL: deleting rows based on a condition with data from another table and NO JOIN

From Dev

How to SELECT none matching rows from one table to another?

From Dev

Selecting rows from one table where value and order from another in MYSQL

From Dev

MySQL select row with two matching joined rows from another table

From Dev

MySQL - Exclude all rows from one table if match on another table

From Dev

Mysql Trigger, Copy All Rows From One Table Into Another Table

From Dev

PostgreSQL: deleting rows referenced from another table

From Dev

MYSQL: Return all rows in one table along with the sum of matching rows in another

From Dev

Copy only specific rows from one table to another in PHP and MySQL

From Dev

Deleting rows from 3 tables in MySQL table

From Dev

Insert data from a table to another one in MySQL with where condition

From Dev

Transferring data from one table to another using a where clause mysql

From Dev

Subtract rows of one table from another table

From Dev

Adding rows from one table to another existing table where primary key is autogenerated

From Dev

Adding rows from one table to another existing table where primary key is autogenerated

From Dev

MySQL Show rows that exist in one table but not it another

From Dev

MySQL Show rows that exist in one table but not it another

From Dev

Mysql rows from table A with ID from query where ID matches one condition true in table B

From Dev

How to copy rows from one table to another?

From Dev

Update specific rows from one table to another

From Dev

How can I move rows from one table to another where they don't exist in a third?

From Dev

How to efficiently delete expired rows from a large MySQL table

From Dev

MySQL - Select multiple rows from one table whose IDs are stored in another table

From Dev

Mysql : select from five different table where matching only one column based on a date or between dates

From Dev

Moving rows from one dataframe to another based on a matching column

From Dev

Delete rows in MySQL matching two columns in another table

Related Related

  1. 1

    MySQL: Update rows in table, from rows with matching key in another table

  2. 2

    How to delete rows from one table matching another table?

  3. 3

    mysql - move rows from one table to another

  4. 4

    MySQL: deleting rows based on a condition with data from another table and NO JOIN

  5. 5

    How to SELECT none matching rows from one table to another?

  6. 6

    Selecting rows from one table where value and order from another in MYSQL

  7. 7

    MySQL select row with two matching joined rows from another table

  8. 8

    MySQL - Exclude all rows from one table if match on another table

  9. 9

    Mysql Trigger, Copy All Rows From One Table Into Another Table

  10. 10

    PostgreSQL: deleting rows referenced from another table

  11. 11

    MYSQL: Return all rows in one table along with the sum of matching rows in another

  12. 12

    Copy only specific rows from one table to another in PHP and MySQL

  13. 13

    Deleting rows from 3 tables in MySQL table

  14. 14

    Insert data from a table to another one in MySQL with where condition

  15. 15

    Transferring data from one table to another using a where clause mysql

  16. 16

    Subtract rows of one table from another table

  17. 17

    Adding rows from one table to another existing table where primary key is autogenerated

  18. 18

    Adding rows from one table to another existing table where primary key is autogenerated

  19. 19

    MySQL Show rows that exist in one table but not it another

  20. 20

    MySQL Show rows that exist in one table but not it another

  21. 21

    Mysql rows from table A with ID from query where ID matches one condition true in table B

  22. 22

    How to copy rows from one table to another?

  23. 23

    Update specific rows from one table to another

  24. 24

    How can I move rows from one table to another where they don't exist in a third?

  25. 25

    How to efficiently delete expired rows from a large MySQL table

  26. 26

    MySQL - Select multiple rows from one table whose IDs are stored in another table

  27. 27

    Mysql : select from five different table where matching only one column based on a date or between dates

  28. 28

    Moving rows from one dataframe to another based on a matching column

  29. 29

    Delete rows in MySQL matching two columns in another table

HotTag

Archive