CODEWITHSUNDEEP
mysqlsqldatabase
Andre Dublin
Andre Dublin

Reputation: 1158

Is there any way to speed up this query?

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.

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.

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
)

Upvotes: 1

Views: 162

Answers (3)

Stephen Quan
Stephen Quan

Reputation: 25966

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

Upvotes: 0

Benoit
Benoit

Reputation: 79185

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.

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).

About index creations.

Another option could be:

SELECT listing_listnum FROM mlscopy
 MINUS
SELECT listing_listnum FROM mls_cvrmls

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

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

Upvotes: 2

Related Questions