On Sat, 19 Oct 2013 20:07:22 +0200
Clemens Ladisch <clem...@ladisch.de> wrote:
> > It seems to be using the the covering index which I guess is the
> > fastest way as Sqlite doesnt need to check the actual table. So why
> > would it slow down and use so much CPU in my last query ?
>
> In theory, a multi-column index can be used to speed up searches for
> values in multiple columns. However, an inequality (like n >=
> 15591116) can be used only in the _last_ column to be searched in an
> index, so this is the only column in this particular query.
Just to clarify, the index is helpful only insofar as its terms are
mentioned in the query, starting leftmost. If you have three columns
A, B, and C in your index, you can have e.g.
where A < 10 and B = 9
and not mention C, and the index can be used. If OTOH you say only
B = 9
that value of B could appear anywhere in the index, for any value of
A. In theory, the index could *still* speed up the query under certain
circumstances. For example, if the query is
select count(B) from T
it might be faster to scan the index than to scan the table. But afaik
SQLite won't do that.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
