CODEWITHSUNDEEP
mysql
Mark Messa
Mark Messa

Reputation: 470

MySQL: Delete an Select Clause

Consider a table table1 and its corresponding lookup table table2: 

`table1`
columns: col1 | id
number of rows: ~2Mi

`table2`
columns: id <primary key> | col2
number of rows: ~1Mi

Since there are several ids in table2 which are not being used in table1, they can be deleted. This can be done with the following query: 

DELETE FROM `table2` WHERE `id` IN (
    SELECT * FROM (
        SELECT `table2`.`id` FROM `table2`
        LEFT JOIN `table1`
        ON `table1`.`id` = `table2`.`id`
        WHERE `table1`.`id` IS NULL
    ) AS p
)

However, this query is very slow (~100 rows per minute).

Question
Why this query is so slow? How to improve it?

Upvotes: 2

Views: 38

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could rewrite it as:

DELETE FROM `table2` 
WHERE NOT EXISTS(SELECT 1 FROM `table1` WHERE `table1`.id = `table2`.id);

Adding index on table2(id) and table1(id) will also help.

Upvotes: 3

Related Questions