On Jul 11, 2015 6:16 PM, "James K. Lowden" <jklowden at schemamania.org> wrote: > > On Fri, 10 Jul 2015 09:54:27 -0600 > Scott Robison <scott at casaderobison.com> wrote: > > > As described, the user will be able to create arbitrarily > > complex queries. Since it is impossible to know in advance what > > indexes might be required, breaking it down to individual sub queries > > with simple where clauses that can be virtually guaranteed to use a > > simple index > > I don't follow you. A complex query is an assemblage of clauses. > Whether or not broken down "to individual sub queries", the search > arguments are the same. They are few in kind, even though they may be > large in number. The analysis for what indexes to create will be the > same either way. In fact, Keith already proposed a plausible design.
I'm just saying that knowing which index to create in advance is impossible if the user can specify arbitrarily complex where clauses. Certainly SQLite will get the right answer, but I can see where breaking it down into individual queries could avoid table scans without needing to create mCn (or maybe mPn) indexes. > > My concerns would be two: > > 1. OR clauses are essentially UNION. I don't know how sophisticated > SQLite is about using different indexes for different OR-separated > criteria or parts of a UNION. Maybe separate queries would be > better. > > 2. LIKE clauses usually don't engage indexes. The OP should > understand that pattern-matching queries will often entail a table scan > unless ANDed to an indexed term. > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
