When you say "All an index does" don't forget that an index is also usually smaller than the data, thereby increase cache performance and reducing disk seeks.
For a good chunk of typical uses (large tables with simple lookups) an index is notably faster. I'll admit my use of sqtlite3 hasn't been on horrendously complex databases but I can say an index beats the pants off of non-indexed for all my usage (at least where I would expect it to). Much as one would expect. I tend to have 1000's to millions of rows with simple ID lookups. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Friday, March 25, 2011 6:33 PM To: j...@kreibi.ch; General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Full Table Scan after Analyze On 25 Mar 2011, at 11:11pm, Jay A. Kreibich wrote: > On Fri, Mar 25, 2011 at 10:30:59PM +0000, Simon Slavin scratched on the wall: > >> Actually I'm surprised and not terribly impressed that SQLite ever >> does a scan when there's an ideal index available. > > Why? Do you want it to run slower? > > Indexes are not magic bullets. Using an index to retrieve a row is > typically 5x to 20x more expensive than scanning a row. There are > plenty of instances when a scan will be faster than an index use, and > not just in small tables. Just as SQLite tries to use any index it > can to speed up a query, it also tries to avoid using indexes that > will slow it down-- and there are plenty of ways this can happen. You know, I'd never thought of that. All an index does is let you search a B-tree rather than a list. Thank you. Simon. _______________________________________________ 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
