I was reading through this article - http://ift.tt/14VgixH
I am working with an already 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 contain over 900K records and Table b contains over 200K and growing.
The above query takes about 7-8 minutes.
I also tried using NOT IN
, which is slow as well.
order_id
column is NOT NULL and UNIQUE indexed.
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: PRIMARY
key_len: 152
ref: func
rows: 1
Extra: Using where; Not exists; Using index
Any help would be great.
Aucun commentaire:
Enregistrer un commentaire