Best way to use carray
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.