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
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `customer` ADD INDEX `customer_idx_customer_id` (`customer_id`);
SELECT
1
FROM
`customer`
WHERE
`customer`.customer_id NOT IN (
SELECT
DISTINCT `order`.customer_id
FROM
`order`
)