vendredi 27 mars 2015

Subquery optimization


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