On Sun, Jul 12, 2015 at 9:46 AM, James K. Lowden <jklowden at schemamania.org>
wrote:
> On Sat, 11 Jul 2015 19:02:59 -0600
> Scott Robison <scott at casaderobison.com> wrote:
>
> > > 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.
>
> In that case, I can make your job easier, starting today. You don't
> need to consider query complexity to design your indexes. :-)
>
{snipped}
You're making someone else's job easier (the OP), not mine. I'm certain I
could (if I cared to spend the time) find a case where the OP scenario
could be more efficiently performed with a set of individual queries that
are only filtered at the end. I don't know how large the data set would
need to be, or how many columns it would require, or how complex the where
clause would be. Just saying that it (almost certainly) exists. Also saying
that the OP scenario of individual queries isn't absolutely a *bad* way to
use SQLite, but there are many ways to skin this particular cat.
Note that *I* am not trying to provide this type of functionality. I'm
perfectly content, when using SQLite, to craft specific queries to get the
job done that can rely on one (or at most a few) specific CREATE INDEX
statements. Often the size of data I'm dealing with is small enough that
the difference between full scan and indexed access is irrelevant.
In any case, certainly the OP has been given plenty to think about and can
probably come up with an efficient way of using SQLite for this particular
use case. Perhaps some of the other suggestions have put the problem in a
different light, in which case monolithic queries can be constructed that
are sufficiently performant.
--
Scott Robison
