On 12 Jul 2015, at 1:16am, James K. Lowden <jklowden at schemamania.org> wrote:
> 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. I think Scott means that each simple query "can be virtually guaranteed to use a simple index". Which is true if you make one index per column. But the time saved by executing each term quicky is lost at the stage where you combine the results because as various people including Scott have posted, UNION and INTERSECT can be slow. I still think that turning the user's search criteria into one long "WHERE" clause is the way to go. That exploits SQLite's existing clever optimization code to the utmost. Simon.
