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
