SQLite NOT IN query is slow

Question

I have two tables - Keys and KeysTemp. KeysTemp contains temporary data which should be merged with Keys using the Hash field. Here is the query:

SELECT
    r.[Id]
FROM
    [KeysTemp] AS k
WHERE
    r.[Hash] NOT IN (SELECT [Hash] FROM [Keys] WHERE [SourceId] = 10)

I have indexes on both tables for SourceId and Hash fields:

CREATE INDEX [IdxKeysTempSourceIdHash] ON [KeysTemp]
(
    [SourceId],
    [Hash]
);

The same index for Keys table, but query is still very slow. There is 5 rows in temporary table and about 60000 in the main table. Query by hash takes about 27 milliseconds, but querying this 5 rows takes about 3 seconds.

I also tried splitting index, i.e. creating different indexes for SourceId and Hash, but it works the same way. OUTER JOIN works even worse here. How to solve that issue?

UPDATE If I remove WHERE [SourceId] = 10 from the query it completes in 30ms, that's great, but I need this condition :)

Thanks

Answer 1

Maybe

select k.id
from keytemp as k left outer join keys as kk on (k.hash=kk.hash and kk.sourceid=10)
where kk.hash is null;

? Assuming, that r is k. Also have you tried not exists? I have no idea if it works different way…