There are several conditions that should be met for walking the index to be faster than walking the table.
1) The most important one: index b-tree structure should be organized in such way that one can walk directly from one leaf to another thus traversing all leaves without touching interior pages. 2) All selected data should exist in the index or there's a condition with good selectiveness containing only data in the index. 3) Index size should be much smaller than table size. 4) Either both index and table pages should be in the cache or table pages should be out of the cache. If table pages are in the cache and index pages are not then probably walking the table will be faster. I'm afraid that the first condition is a show stopper and it's not implemented in SQLite. Pavel On Fri, Oct 15, 2010 at 10:18 AM, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > I see the difference now... > > So I take it that it's faster just to walk the table once rather than walk > the index? > > Couldn't you just walk the index once? Smaller data space (quite likely), > better caching? It might be a wash or worse with the potential of having to > retreive other fields from the table I suppose. But in the case where you're > just retrieving the same fields as the index wouldn't that generally be > faster? > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > ________________________________ > > From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov > Sent: Fri 10/15/2010 8:27 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] How to optimize a multi-condition query > > > >> sqlite> create table c(achr char,bchr char); >> sqlite> create index c_chr on c(achr,bchr); >> sqlite> explain query plan select achr,bchr from c where achr=bchr; >> 0|0|TABLE c >> >> Why no use of the index in this case? > > How do you think it should be used here? It's not that rows with the > same values of achr and bchr stored together in the index - they are > spread all over the place. And thus using index it will have to make > full scan of it which is less efficient than full scan of the table. > Or should I say it's more efficient than full scan of the table in > very rare situations, so that I'm not sure if SQLite implements it at > all. > > > Pavel > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
