mysql NOT IN QUERY optimize
I have two tables named as:
- table_product
- table_user_ownned_auction
table_product
specific_product_id astatus ...
(primary_key,autoinc)
--------------------------------------
1 APAST ...
2 ALIVE ...
3 ALIVE ...
4 APAST ...
5 APAST ...
t开发者_如何学JAVAable_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.
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
How can i optimize it or any other way to select what i want?
Using NOT EXISTS
SELECT p.*
FROM TABLE_PRODUCT p
WHERE p.astatus = 'APAST'
AND NOT EXISTS (SELECT NULL
FROM TABLE_USER_OWNED_AUCTION uoa
WHERE uoa.specific_product_id = p.specific_product_id)
Using LEFT JOIN/IS NULL
SELECT p.*
FROM TABLE_PRODUCT p
LEFT JOIN TABLE_USER_OWNED_AUCTION uoa ON uoa.specific_product_id = p.specific_product_id
WHERE p.astatus = 'APAST'
AND uoa.own_id IS NULL
Explanation
The most optimal query can be determined by if the columns compared between the two tables are NULLable (IE: if the values of specific_product_id
in either table can be NULL
).
- If nullable,
NOT IN
orNOT EXISTS
is the best choice in MySQL - If NOT nullable, `LEFT JOIN/IS NULL is the best choice in MySQL
Addendum
Once the optimal query has been determined, take a look at creating indexes (possibly covering indexes) for at least:
specific_product_id
TABLE_PRODUCT.astatus
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
精彩评论