Tomash Brechko <[EMAIL PROTECTED]> wrote:
>
> sqlite> explain query plan
> ...> SELECT id, n1, n2
> ...> FROM tbl
> ...> WHERE n1 = $I
> ...> UNION
> ...> SELECT id, n1, n2
> ...> FROM tbl
> ...> WHERE n2 = $I
> ...> ORDER BY id DESC;
> 0|0|TABLE tbl WITH INDEX idx1
> 0|0|TABLE tbl WITH INDEX idx2
>
Correct. Notice, however, that the UNION is not strictly
equivalent to the original query. The UNION query above
gives the same results as:
SELECT DISTINCT id, n1, n2 FROM tbl WHERE n1=$I OR n2=$I;
Perhaps the added DISTINCT will make no difference in
the output. If so, then the UNION is the best way to go.
But if DISTINCT will combine records that you do not want
to be combined, then you might consider rewriting the query
as follows:
SELECT id, n1, n2 FROM tbl
WHERE rowid in (
SELECT rowid FROM tbl WHERE n1=$I
UNION
SELECT rowid FROM tbl WHERE n2=$I
)
Note that some client/server database engines (ex: PostgreSQL
and I think also firebird) will automatically rewrite the
original query into something logically similar to my
second example above. But the query optimizer in SQLite
does not attempt to be quite that clever.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
