Best way to use carray
(1) By Marco Bubke (marcob) on 2021-09-23 07:35:22 [link] [source]
I have a table foo:
CREATE TABLE foo ( sourceId INTEGER, data TEXT, id INTEGER PRIMARY KEY, );
Now I query the table by
SELECT id, data FROM foo WHERE sourceId IN carray(?1)
The carray is normally a smaller subset but sometimes it can be a really large subset too. After looking into the carray source my understanding is that there is no index optimization. I am not sure if Sqlite is maybe creating a temporary index. Would be there an advantage to provide a sorted_carray implementation?
Best regards, Marco
(2) By curmudgeon on 2021-09-23 10:33:48 in reply to 1 [link] [source]
You could just create a temp sorted table and populate that with the array but that would be slower. If the array itself is sorted you could do a binary search on it in your own code to find the first and last index and bind the resulting start pointer and number of entries.
(3) By Richard Hipp (drh) on 2021-09-23 15:12:42 in reply to 1 [source]
Indexing will happen in the SQLite core, not in the carray extension. Indexing will occur if you create an index:
CREATE INDEX foo_sid ON foo(sourceId);