Original query: SELECT ... FROM mytable WHERE field_a IN (<list of values>)AND field_b IN (<list-of-values>) AND <key constraints>
This is sometimes (when mytable is a virtual table that offers to handle "field_a =" and "field_b =" constraints internallly) resolved as - create table eph_a - create table eph_b - full table scan eph_a - full table scan eph_b - scan table mytable index #n Target query: WITH (...) SELECT ... FROM mytable LEFT JOIN cte_a ON (mytable.a = cte_a.a) LEFT JOIN cte_b ON (mytable.b = cte_b.b) WHERE <key constraints> Which should resolve as - create table eph_a - create table eph_b - scan table mytable index #n - lookup mytable.a in eph_a - lookup mytable.b in eph_b The cost of a partial index scan is O(log n) to locate the first record and O(m) for retrieving m consecutive records. The cost of a lookup in an ephemeral table is O(log n). The cost of a full table scan for an ephemeral table is O(n) The cost of the first query plan is therefore O(a * b * (m + log n)) or O(a *b *m) + O(a * b * log n) The cost of the second query plan is only O(log n + m * (log a + log b)) or O((log a*b) * m) + O(log n) This makes the second query plan much less costly for IN lists of 2 or more elements. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Donnerstag, 06. Dezember 2018 11:32 An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Re: sqlite3_bind_text() and WHERE x IN (?) On Thu, Dec 6, 2018 at 11:10 AM Hick Gunter <h...@scigames.at> wrote: > Maybe someone can come up with a CTE that works for this... > How so? I'm not following you. Ryan already provided a CTE to transform a string into values, but that involves string parsing, and is kinda ugly (no offence Ryan :) ), and unlikely to be that efficient relative to carray. The point is that binding is *scalar only* in SQLite. If OTOH, we could write: ...prepare...(db, "select ... from t where c in (?)", ..., &stmt, ...); ...bind_array(stmt, 1, SQLITE_INTEGER, vec.size()); for (auto elem : vec) { ...bind_int(stmt, 1, elem); } ...bind_array would instantiate the same array the carray() extension supports. Existing typed bind APIs would fill in that array, with usual SQLite conversions, error checking against declared array-type. And ...prepare would transparently transform WHERE c IN (?) with WHERE c IN (carray(?)) with the internally managed C array. Of course, at prepare time, SQLite doesn't know yet ? is not a scalar as usual, so perhaps a different notation is necessary, like the TABLE() operator in Oracle SQL for example. But given carray() and the existing infrastructure, I naively fail to see how the above wouldn't work. My $0.02. --DD [1] https://www.sqlite.org/c3ref/c_blob.html _______________________________________________ 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
