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?
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` )