On Wed, 2014-09-24 at 19:36 -0600, Keith Medcalf wrote:
>
> Interesting. From that code you might want to try something like this:
>
> SELECT uid, vcard, bdata
> FROM folder_id
> WHERE uid in ( select uid FROM email_list where value like 'p%'
> union
> select uid from folder_id where nickname LIKE 'p%'
> union
> select uid from folder_id where full_name LIKE 'p%'
> union
> select uid from folder_id where family_name LIKE 'p%'
> union
> select uid from folder_id where given_name LIKE 'p%'
> union
> select uid from folder_id where nickname LIKE 'p%'
> union
> select uid from folder_id where file_as LIKE 'p%'
> );
>
> Then having nocase indexes on the various search fields will all work as
> expected.
Yeah, that achieves the same speed.
I'm not sure it addresses the real problem though. It still only really
applies when the user's query (which we're translating to SQL) contains
only 'OR' and no 'AND' clauses. It doesn't help me translate a query in
the general case.
Now, I'm not *entirely* averse to having a special case for the 'all OR'
query — this particular query is the address autocompletion so it's
common and the user is actually waiting for it as they type. In fact, as
a proof of concept I've already *implemented* a hackish special case to
spot this case and basically submit a hand-crafted query instead of the
normal translation to SQL:
https://bugzilla.gnome.org/show_bug.cgi?id=699597#c19
The problem is that I don't *want* to have to have that special case.
This is just a query optimisation, which is something the query planner
is supposed to do. I don't *want* to implement this and other
optimisations in the client, just to trick *today's* sqlite query
planner into spotting the best way to do it. That's the Wrong Way™ to do
things.
There are two alternative approaches which *don't* seem as wrong.
Firstly, if there's a sane way to rewrite our translator so that it
naturally uses UNION for OR clauses, that might make sense. But to cope
with AND clauses, AFAICT the natural extension of that approach would be
to use 'SELECT FROM ... SELECT FROM' and then we lose the use of indices
for *those* cases, right¹? Tristan started a thread about this 'nested
select' last year, which I picked up a couple of weeks ago. It didn't
seem like it was a viable strategy for the general case.
The second approach, and this is why I started this thread, is to 'fix'
the query planner so that that it can see for *itself* the best way to
implement a given query given the constraints.
I suggested a couple of specific optimisations which the query planner
might be able to make, which should hopefully have benefits wider than
just my own use case. Are those not viable?
--
dwmw2
¹ I do realise that in the special case of a single top-level AND such
that all its sub-clauses are necessary conditions, I can do something
nicer. But again, it's a special case and doesn't handle the general
case of nested AND and OR clauses. And it's still the *client* code
doing the job of the optimiser, spotting necessary vs. sufficient
conditions and pulling them out to the top level for more efficient
implementation.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
