CODEWITHSUNDEEP
mysqlinner-join
arkhamvm
arkhamvm

Reputation: 669

Negative INNER JOIN [Without LEFT JOIN & IS NULL]

I have 2 tables with tth hashes, and i need to get which of them, who exist in first table but not exist in second table. I'll try something like this:

SELECT f.*
FROM files as f
LEFT JOIN trans as t ON t.tth=f.tth
WHERE t.id IS NULL

But it's working very slow, in first table 65k lines, and second table with 130k lines, so query working for ~5 minutes. Here exist another way? Thanks.

P.S. All columns in both tables having indexes.

Upvotes: 4

Views: 8477

Answers (1)

arkhamvm
arkhamvm

Reputation: 669

Thanks, OMG Ponies, I read an article on the link, and a little optimizing method using NOT IN, query rate was ~ 0.6 seconds.

    SELECT f.*
    FROM files as f
    WHERE 
    f.tth NOT IN (
      SELECT trans.tth
      FROM trans as t
-- We do not need to look entries across entire table, only from already matched, so create selection "limits" with INNER JOIN.
      INNER JOIN files ON files.tth=t.tth
    )

Upvotes: 4

Related Questions