I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

I am working with an existing design.

What I want is to be able to select all id's from table a, which are not in table b.

I am using this query:

SELECT a.* FROM `orders` a LEFT JOIN `orders_corrected` b ON 
a.`order_id`=b.`order_id` WHERE b.`order_id` IS NULL;

Table a contains over 900K records and Table b contains over 200K and growing.

The above query takes about 7-8 minutes. I tried doing a LIMIT 5 as well. but it doesn't help much.

I also tried using NOT IN, which is slow as well.

order_id column is NOT NULL and UNIQUE indexed.

Ouput of show create table...

CREATE TABLE `orders` (
 `order_id` varchar(50) NOT NULL DEFAULT '',
 `order_data` text,
 `billing_address` text,
 `shipping_address` text,
 `updated_on` datetime DEFAULT NULL,
 PRIMARY KEY (`order_id`),
 KEY `ix_order_id` (`order_id`)

Output of explain:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 595783
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY,order_id_UNIQUE,ix_order_id
key_len: 152
ref: func
rows: 1
Extra: Using where; Not exists; Using index
  • Do you have an index on order_id in both tables? – ypercubeᵀᴹ Mar 27 '15 at 14:37
  • Add the SHOW CREATE TABLE tablename; output for both tables. (what is the datatype of order_id? VARCHAR(50)? ) – ypercubeᵀᴹ Mar 27 '15 at 14:38
  • @ypercube Yes, both are indexed (unique key)...varchar 50 – web-nomad Mar 27 '15 at 14:42
  • Edit the question and add the SHOW CREATE TABLE tablename; for both tables. – ypercubeᵀᴹ Mar 27 '15 at 14:43
  • Edited. Have added the SHOW CREATE... for only one table as the other one is exactly the same. – web-nomad Mar 27 '15 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.