On Thu, May 22, 2014 at 5:34 PM, Abramo Bagnara
<[email protected]>wrote:
> $ sqlite3
> SQLite version 3.8.4.3 2014-04-03 16:53:12
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t(a, b, c);
> sqlite> create index t_ab on t(a, b);
> sqlite> create index t_ac on t(a, c);
> sqlite> explain query plan select * from t where a = 1 and (b = 2 or c =
> 2);
> 0|0|0|SEARCH TABLE t USING INDEX t_ac (a=?)
> sqlite> explain query plan select * from t where (a = 1 and b = 2) or (a
> = 1 and c = 2);
> 0|0|0|SEARCH TABLE t USING INDEX t_ab (a=? AND b=?)
> 0|0|0|SEARCH TABLE t USING INDEX t_ac (a=? AND c=?)
>
> Although the two queries are equivalent the first form is not optimized
> to use available indices.
>
> Is this expected?
>
Actually, SQLite believes (with good reason) that the first form is faster
than the second. Using two indices to process a WHERE clause with OR terms
is much faster than a full table scan, but it is also much more costly than
using just a single index. So the first form is usually preferred. (That
decision might come out differently if SQLite has access to index
statistics created by ANALYZE but it comes out as shown above by default,
and that is a reasonable default choice in the absence of additional
information.)
Curiously, SQLite will convert (a=? AND (b=? or c=?)) into ((a=? AND b=?)
OR (a=? AND c=?)) as you can see by running the following:
create table t(a,b,c);
create index t_ac on t(a,c);
create index t_bc on t(b,c);
explain query plan select * from t where (a=1 or b=2) and c=3;
0|0|0|SEARCH INDEX t USING INDEX t_ac (a=? AND c=?)
0|0|0|SEARCH INDEX t USING INDEX t_bc (b=? AND c=?)
But the query planner does not try to factor the WHERE clause and convert
((a=? AND b=?) OR (a=? AND c=?)) into (a=? AND (b=? OR c=?)), which would
be necessary in order to get your second query to use the faster plan.
>
> --
> Abramo Bagnara
>
> BUGSENG srl - http://bugseng.com
> mailto:[email protected]
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
