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. 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
