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. The whole idea of an "ideal index" is something of a falsehood. It all depends on context. You cannot tell from the schema and query alone if an index will help. You can make good guesses, but you can't *know* without understanding the size, shape, and distribution of the actual data in the tables. That's the whole point of ANALYZE... to provide that context data to the query optimizer. If SQLite *correctly* gathered stats that say a full scan would be faster, why would you want it to do things the slow way? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
