Gowri Gowri - 4 months ago 8
MySQL Question

mysql NOT IN QUERY optimize

I have two tables named as:


  1. table_product

  2. table_user_ownned_auction



table_product



specific_product_id astatus ...
(primary_key,autoinc)
--------------------------------------
1 APAST ...
2 ALIVE ...
3 ALIVE ...
4 APAST ...
5 APAST ...


table_user_ownned_auction



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

SELECT *
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.

EXPLAIN PLAN



enter image description here

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

Answer

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:

SELECT p.*
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