On Wed, May 09, 2007 at 16:34:57 +0400, bash wrote: > On Wed, 9 May 2007 14:24:27 +0400 > Tomash Brechko <[EMAIL PROTECTED]> wrote: > > From http://www.sqlite.org/optoverview.html section 6.0: > > > > Each table in the FROM clause of a query can use at most one index... > > > > So the first query can't benefit from both idx1 and idx2. You may use > > > > EXPLAIN QUERY PLAN SELECT ... > > > > to see what indexes will be used. > > If i understand right from previous post by Peter there will be not > used any indexes because of "OR".
I think those slides are a bit outdated. On the same http://www.sqlite.org/optoverview.html page the section "3.0: The OR optimization" says that the query from the slide 52 SELECT * FROM ex4 WHERE x=5 OR x=7; will be rewritten as SELECT * FROM ex4 WHERE x IN (5, 7); and IN can use indexes. But "x=5 OR y=7" (i.e. conditions on _different_ columns) can't be rewritten that way---exactly your situation. On Wed, May 09, 2007 at 16:32:34 +0400, bash wrote: > SELECT * FROM ex1 WHERE x>'abc' AND y>'abc'; > In this form only one indexes will be used, why not both? One index per table rule. At first glance it seems like SQLite could use at least one index for "x=5 OR y=7" case too, but there is no point in that, as the other part of the OR would require full table scan anyway. And for the solution with the UNION, 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 both indexes are used. -- Tomash Brechko ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
