MySQL优化查询不在 [英] MySQL optimized query for not in

问题描述

我有一个VBA程序的excel，该程序连接到MySQL数据库以检索一些信息.最初，当程序加载时，我有以下查询.

I have an excel with VBA program which connects to MySQL database to retrieve some information. Initially, when the program loads I have the below query.

SELECT A.id,A.first_name,A.last_name FROM Table1 A WHERE A.ID NOT IN(SELECT DISTINCT  
SUBJECT FROM Table2)

表1的数据如下.

• 1位基督徒大包
• 2克里斯托弗·诺兰(Christopher Nolan)
• 等

表2具有如下数据.

• 1名acted_in蝙蝠侠
• 2名导演的蝙蝠侠
• 等

只要表2中的总行较少，上面给出的查询就可以很好地工作.但是，当前，我的数据在表1中有26000行，在表2中有102000行.因此，当我在表2中运行上述查询时程序，执行查询大约需要半小时，有时可能无法正常执行.我也尝试了以下查询，但它也需要很长时间.

My query given above works perfectly fine as long as the total rows are less in table 2. However, currently my data has 26000 rows in table 1 and 102000 rows in table 2. So, when I run the above query in my program, it takes around half an hour to execute the query and sometimes it doesn't execute properly. I tried the below query too but it also takes long time.

SELECT  A.id, A.first_name, A.last_name FROM Table1 a 
WHERE   NOT EXISTS
(
    SELECT  1
    FROM    Table2 al
    WHERE   a.id = al.subject
)

是否有一种有效的方法来重新表达查询?

Is there an efficient way to rephrase the query?

推荐答案

使用外部联接

SELECT A.*
FROM Table1 a
LEFT OUTER JOIN (SELECT DISTINCT subject
            FROM Table2) b ON a.id = b.subject
WHERE b.subject IS NULL

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