MySQL不在极慢的地方 [英] MySQL WHERE NOT IN extremely slow

问题描述

下面是存储过程中的一条SQL语句(为简洁起见，将其删节了):

Below is a SQL statement inside a stored procedure (truncated for brevity):

SELECT * 
FROM item a 
WHERE a.orderId NOT IN (SELECT orderId FROM table_excluded_item);

此语句需要30秒钟左右！但是，如果我删除内部的SELECT查询，它将降至1s. table_excluded_item并不大，但是我怀疑内部查询的执行量超出了需要.

This statement takes 30 seconds or so! But if I remove the inner SELECT query, it drops to 1s. table_excluded_item is not huge, but I suspect the inner query is being executed more than it needs to be.

是否有更有效的方法?

推荐答案

使用LEFT JOIN

SELECT  a.* 
FROM    item a 
        LEFT JOIN table_excluded_item b
            ON a.orderId = b.orderId
WHERE   b.orderId IS NULL

确保两个表中的orderId已被索引.

make sure that orderId from both tables has been indexed.

这篇关于MySQL不在极慢的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！