It will, but that depends how many rows there are.
That is, the statement: SELECT * FROM t1 WHERE id IN (1,2,3,4,5,6)
Is equivalent to
CREATE TEMPORARY TABLE keyset (key PRIMARY KEY);
INSERT OR IGNORE INTO keyset VALUES (1), (2), (3), (4), (5), (6);
SELECT * FROM t1 WHERE id IN keyset;
DROP TABLE keyset;
without the overhead of parsing the extra create/insert/drop statements. And
of course the part:
SELECT * FROM t1 WHERE id IN keyset;
is really
SELECT * FROM t1 JOIN keyset ON t1.id == keyset.key;
which is just sugar for:
SELECT * FROM t1, keyset where t1.id == keyset.key;
That means that the query planner may place the keyset in the outer loop, or it
may place t1 in the outer loop, depending on which is "better" and what else
the query is doing. In other words the list (...) just becomes an index
containing the values that is subsequently treated as a table (and for that
purpose the list (1,3,5,7,9) is the same as list (9,1,7,3,5,1,9,3,7) -- since
it is sorted and unique). Note that the list may contain NULLs but they are
ignored.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Jens Alfke
>Sent: Monday, 16 September, 2019 10:58
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?
>
>
>
>> On Sep 13, 2019, at 10:57 AM, Hick Gunter <h...@scigames.at> wrote:
>>
>> This is faster if the number of keys in the list is small relative to
>the number of records in the table.
>> If the number of keys is similar to the number of records in the table,
>then a simple full table scan may be faster.
>
>Experimentally, the optimizer seems to choose an index search even with
>the simpler query. I ran this on a test database with about 30k rows.
>
>> explain query plan select * from kv_default where key in ('a','b','c')
>
>3|0|0|SEARCH TABLE kv_default USING INDEX sqlite_autoindex_kv_default_1
>(key=?)
>
>—Jens
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
