On Mon, 2005-07-11 at 14:20 +0100, Steve O'Hara wrote:
> Is that statement correct Richard?
> I assumed that something clever was going on with OR's such that under the
> bonnet they got translated into UNION statements or similar.
>
SQLite does not currently optimizer ORs in any way. If you
put ORs in your WHERE clause, no indices will be used.
Work is underway to improve on this somewhat. If you say
something like:
SELECT * FROM a WHERE f1=5 OR f1=11;
Then future versions of SQLite will use an index on a(f1).
Indices are not currently used unless you say:
SELECT * FROM a WHERE f1 IN (5,11);
The optimization currently under development is to automatically
translate queries of the first form into queries of the second
so that indices will be used.
Optimizing queries like this:
SELECT * FROM a WHERE f1=5 OR f2=11;
where two different columns are used in the OR clause is more
problematic. MySQL does not do this I have found. I do not
know about PostgreSQL - if somebody with PostgreSQL installed
could email the EXPLAIN output of such a query, I would appreciate
it.
One might consider evaluating the query like this:
SELECT * FROM a WHERE f1=5 UNION SELECT * FROM a WHERE f2=11;
Or like this:
SELECT * FROM a WHERE f1=5 UNION ALL
SELECT * FROM a WHERE f2=11;
But neither of those are equivalent to what we want. The only
way I see to do this is to rewrite the original query as
follows:
SELECT * FROM a WHERE rowid IN
(SELECT rowid FROM a WHERE f1=5 UNION
SELECT rowid FROM a WHERE f2=11);
Perhaps SQLite will do such rewriting automatically someday,
but probably not in the near term.
--
D. Richard Hipp <[EMAIL PROTECTED]>
