Gowri Gowri - 1 year ago 41
MySQL Question

mysql NOT IN QUERY optimize

I have two tables named as:

  1. table_product

  2. table_user_ownned_auction


specific_product_id astatus ...
1 APAST ...
2 ALIVE ...
3 ALIVE ...
4 APAST ...
5 APAST ...


own_id specific_product_id details
1 1 XXXX
2 5 XXXX

I need to select
atatus = APAST
, and not in table 2.

Which means, in above structure table1 has 3 APAST status (1,4,5). But in table 2 specific_product_id (1,5) only stored so i need to select specific_product_id = 4

I used this query

FROM table_product
WHERE astatus = 'APAST'
AND specific_product_id NOT IN (SELECT specific_product_id
FROM table_user_ownned_auction )

...which takes this long:

Query took 115.1039 sec

...to execute.


enter image description here

How can i optimize it or any other way to select what i want?

Answer Source

Try adding a index on the table_user_ownned_auction table:

ALTER TABLE table_user_ownned_auction ADD KEY(specific_product_id)

Also, try using a non-exists join:

FROM table_product p
    LEFT JOIN table_user_ownned_auction l
      ON p.specific_product_id = l.specific_product_id
WHERE p.astatus = 'APAST' 
    AND l.specific_product_id IS NULL