有没有办法加快这个查询？

I have two tables one being a copy of the other. (I have to remove records that no longer exist in the parent). Running this query works, but requires about 1 min to complete. I know the NOT EXISTS is faster because I won't be running the sub-query for each row, but doesn't return any results.

我有两张桌子,一张是另一张桌子的副本。 (我必须删除父母中不再存在的记录)。运行此查询有效,但需要大约1分钟才能完成。我知道NOT EXISTS更快,因为我不会为每一行运行子查询,但不会返回任何结果。

SELECT mlscopy.listing_listnum 
FROM mlscopy 
WHERE mlscopy.listing_listnum 
NOT IN (SELECT mls_cvrmls.listing_listnum FROM mls_cvrmls)

I'll clarify, the problem here is the parent changes over time, and I have to remove/add records from the child. So I'm stuck on removing from child where listing_listnum doesn't exist in parent.

我要澄清一下,这里的问题是父母随着时间的推移而变化,我必须从孩子那里删除/添加记录。所以我坚持要从child中删除list_listnum不存在于parent中。

Here is the not exists query

这是不存在的查询

SELECT mlscopy.listing_listnum 
FROM mlscopy 
WHERE 
NOT EXISTS (SELECT mls_cvrmls.listing_listnum FROM mls_cvrmls)

Figured it out

弄清楚了

SELECT mlscopy.listing_listnum
FROM mlscopy 
WHERE NOT EXISTS (
SELECT mls_cvrmls.listing_listnum 
FROM mls_cvrmls
WHERE mlscopy.listing_listnum = mls_cvrmls.listing_listnum
)

3 个解决方案

#1

SELECT mlscopy.listing_listnum 
FROM mlscopy A
NOT EXISTS
(
    SELECT *
    FROM mls_cvrmls B
    WHERE B.listing_listnum = A.listing_listnum
);

#2

Try this variation and see if it's any better:

尝试这种变化,看看它是否更好:

SELECT mlscopy.listing_listnum 
    FROM mlscopy 
        LEFT JOIN mls_cvrmls
            ON mlscopy.listing_listnum = mls_cvrmls.listing_listnum
    WHERE mls_cvrmls.listing_listnum IS NULL

#3

Try this:

CREATE INDEX i_listnum ON mls_cvrmls(listing_listnum ASC)

Trying to rewrite the query with a LEFT JOIN or NOT EXISTS will probably not make that much a difference because the query optimizer could figure that out.

尝试使用LEFT JOIN或NOT EXISTS重写查询可能不会产生太大的影响,因为查询优化器可以解决这个问题。

However having a mean to quickly find a row in mls_cvrmls by listing_listnum can only improve the performance (but it will take additional space).

但是,通过listing_listnum快速查找mls_cvrmls中的行只能改善性能(但需要额外的空间)。

About index creations.

关于索引创作。

Another option could be:

另一种选择可能是:

SELECT listing_listnum FROM mlscopy
 MINUS
SELECT listing_listnum FROM mls_cvrmls

#1

SELECT mlscopy.listing_listnum 
FROM mlscopy A
NOT EXISTS
(
    SELECT *
    FROM mls_cvrmls B
    WHERE B.listing_listnum = A.listing_listnum
);

#2