"A. Pagaltzis" <[EMAIL PROTECTED]> wrote:
> * Brannon King <[EMAIL PROTECTED]> [2006-05-26 21:35]:
> > I would think this would be a valuable optimization for Sqlite
> > to perform automatically. When it finds an OR operation, it
> > should look to see if duplicating the query and using the UNION
> > ALL compounder would allow it to use additional indexes and if
> > so then duplicate it.
>
> But it canât: using `UNION ALL` will return duplicates whereas
> using `OR` wonât, so you canât substitute the former for the
> latter.
>
The right way to do this query is:
SELECT * FROM vals2d WHERE rowid IN
(SELECT rowid FROM vals2d WHERE x=1
UNION ALL SELECT rowid FROM vals2d WHERE y=1);
I have been aware of the potential to do this kind
of optimization automatically for years. But to do
so would require a lot of code, and it does not
accomplish anything that you cannot already do
manually, and it just does not come up that often.
So I have decided to forego it for the time being.
--
D. Richard Hipp <[EMAIL PROTECTED]>
