WITH list (key) AS (VALUES (<value),...) SELECT table.key, ... FROM list cross join table on (list.key = table.key);
This forces SQLite to use the list as the outer loop and perform a key lookup. 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. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet: Freitag, 13. September 2019 18:39 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Fastest way to SELECT on a set of keys? If I have a set of primary keys (let's say a few hundred) and need to fetch data from the table rows with those keys, what's the fastest way to do so? The options seem to be: (a) Execute "SELECT … FROM table WHERE key=?", once for each key. (b) Execute "SELECT key, … FROM table WHERE key IN (…)", including all of the key strings. If I do (a), I can pre-prepare the statement and save the overhead of compilation. But SQLite has to go through the rest of its work (starting the virtual machine, b-tree lookup, etc.) once for each key. If I do (b), SQLite has less setup work to do, and it could potentially optimize the b-tree lookup. On the downside, I have to prepare a statement every time since the RHS of an "IN" isn't substitutable. Does anyone have intuition or actual knowledge about which approach is better? Or know of a 3rd better approach? —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
