Optimizing SQL query

Question

As an assignment I need to clean up a movie database.

Some users has been deleted, and i have to remove ratings from users no longer in the database.

I made this query

DELETE rating FROM rating LEFT JOIN (SELECT id FROM user) as A ON A.id = rating.userId WHERE A.id IS NULL;

I've made indexes on rating.userId and user.Id

As there is 6000 users and 1.000.000 ratings, this takes insanely long time. Can anyone figure out how i can perform this, or a query like this, with better performance?

A.id = rating.userId WHERE A.id IS NULL isn't that equivalent to rating.userId = NULL? — njzk2
– njzk2, Commented Nov 25, 2013 at 14:33
@njzk2 not necessarily, I suspect this code is to delete data from rating where the user no longer exists. If the database had referential integrity configured, OP probably wouldn't have to write this. — Matthew
– Matthew, Commented Nov 25, 2013 at 14:35
Insanely long time is not completing within 30mins. It completed in 23 secs with @juergen-d is answer :) — cholewa1992
– cholewa1992, Commented Nov 25, 2013 at 14:47

juergen d · Accepted Answer · 2013-11-25 14:33:00Z

7

Remove the unnecessary subselect

DELETE rating 
FROM rating 
LEFT JOIN user ON user.id = rating.userId 
WHERE user.id IS NULL

answered Nov 25, 2013 at 14:33

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

Comments

Mad Dog Tannen · Accepted Answer · 2013-11-25 14:38:04Z

0

Run this SELECT query and confirm that these are the rows you want to delete.

SELECT * FROM rating WHERE UserId NOT IN (SELECT Id FROM user);

If they match you can simply run a query like this.

DELETE FROM rating WHERE UserId NOT IN (SELECT Id FROM user);

answered Nov 25, 2013 at 14:38

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

Collectives™ on Stack Overflow

Optimizing SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related