On Wednesday, 29 October, 2014 07:47, Clemens Ladisch said: >Baruch Burstein wrote: >> If I have an index on table1(colA, colB), will it be used for both the >> where and the order by in either of these cases:
>> select * from table1 where colA=1 order by colB; >> select * from table1 where colB=1 order by colA; >$ sqlite3 >sqlite> create table table1(colA, colB, [...]); >sqlite> create index i on table1(colA, colB); >sqlite> explain query plan select * from table1 where colA=1 order by >colB; >0|0|0|SEARCH TABLE table1 USING INDEX i (colA=?) >sqlite> explain query plan select * from table1 where colB=1 order by >colA; >0|0|0|SCAN TABLE table1 USING INDEX i >In the second query, the database reads colB1 from the index, but >this happens for _all_ rows, so the WHERE is not sped up. That depends on your definition of "sped up", the hit rate of the where clause, and the size of the rows in the underlying table. Since the index contains only a couple of bytes of data it can most likely be scanned far faster than the underlying table rows, and the result set does not need to be sorted before being returned. For five rows it will make no difference. For thousands or millions of rows the difference will be significant. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
