CODEWITHSUNDEEP
mysql
Asif Ali
Asif Ali

Reputation: 73

How to optimize this delete query?

I am trying to run this query:

delete FROM `customer` where customer_id 
not in (SELECT distinct customer_id FROM `order`);

But it's taking a long time, leading to timout and breaks in middle because customer table has more than a million records.

How can I make it fast? any alternative?

edit

Here is EXPLAIN of same with SELECT:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     customer    index   NULL    PRIMARY     4   NULL    127659  Using where; Using index
2   DEPENDENT SUBQUERY  order   ALL     NULL    NULL    NULL    NULL    25141   Using where

Upvotes: 0

Views: 50

Answers (2)

user4420255
user4420255

Reputation:

First you can remove DISTINCT from this DELETE command. It's not necessary here. So try this

delete FROM `customer` where customer_id 
not in (SELECT customer_id FROM `order`);

Second please check EXECUTION PLAN with this rebuild query

SELECT customer_id FROM `customer` where customer_id 
not in (SELECT customer_id FROM `order`);

This will show how MySQL executes this query and what's going on.

Upvotes: 0

Jens
Jens

Reputation: 69440

try:

DELETE customer FROM customer t1 LEFT JOIN `order` t2 on t1.customer_id = t2.customer_id WHERE t2.customer_id is null;

Upvotes: 1

Related Questions