On 24 Oct 2012, at 9:59am, "Scholz Maik (CM-AI/PJ-CF42)" <[email protected]> wrote:
> 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? It /does/ use index i1 for the SEARCH step, as it explains in the first line. However, you then ask for the answers to be sorted into f1 order, which it can't do using that index. If you ask for them to be sorted into a order ... sqlite> explain query plan select a,b,f1 from v1 where a=3 order by a; 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) it correctly deduces it doesn't need to use a TEMP B-TREE There's no way to do anything like CREATE INDEX i2 on t1(a,a+b); And you cannot index a view. So there's no way to create an ideal index for your select on a view select a,b,f1 from v1 where a=3 order by (f1); Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
