Hi, I have some strange behavior with the query optimizer. SQLite version 3.7.7.1 2011-06-28 17:39:05
sqlite> create table t1 (a,b); sqlite> insert into t1 (a,b) values (1,2); sqlite> insert into t1 (a,b) values (3,4); sqlite> select * from t1; 1|2 3|4 sqlite> create index i1 on t1(a); sqlite> create view v1 as select a,b,a+b as f1 from t1; sqlite> create view v2 as select a,b,a+b as f1 from t1 order by (a+b); sqlite> create view v3 as select a,b,a+b as f1 from t1 order by f1; sqlite> explain query plan select a,b,f1 from v1 where a=3; 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) => Perfect, index i1 is used! sqlite> explain query plan select a,b,f1 from v1 where a=3 order by (f1); 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY => Why is index i1 not used? sqlite> explain query plan select a,b,f1 from v2 where a=3; 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY => Perfect, index i1 is used! sqlite> explain query plan select a,b,f1 from v2 where a=3 order by (f1); 1|0|0|SCAN TABLE t1 (~1000000 rows) 1|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (a=?) (~7 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY => Why is index i1 not used? sqlite> explain query plan select a,b,f1 from v3 where a=3; 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY => Perfect, index i1 is used! sqlite> explain query plan select a,b,f1 from v3 where a=3 order by (f1); 1|0|0|SCAN TABLE t1 (~1000000 rows) 1|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (a=?) (~7 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY => Why is index i1 not used? Why do I see I different query plan, when a query on a view is done with "ORDER BY". How can I force using the index i1? Thanks in advance. Maik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
