mysql NOT IN QUERY优化 [英] 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

我需要选择atatus = APAST，而不是表2.
这意味着，在上述结构中，表1具有3个APAST状态(1、4、5).但是在表2中，specific_product_id(1,5)只存储了，所以我需要选择specific_product_id = 4

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

我使用了此查询

  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:

查询耗时115.1039秒

Query took 115.1039 sec

...执行.

我如何优化它或以其他任何方式选择我想要的东西?

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

推荐答案

尝试在table_user_ownned_auction表上添加索引:

ALTER TABLE table_user_ownned_auction ADD KEY(specific_product_id)

此外，尝试使用non-exists联接:

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

这篇关于mysql NOT IN QUERY优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！