On Fri, Sep 26, 2008 at 12:54:36PM -0400, Russell Leighton wrote: > I need a 2 key index for some queries and also want to aggregate on > these 2 columns. I need this index BUT I have many large sqlite dbs I > iterate over and they won't fit in the filesystem cache. Run time when > the index is present is 105min. Run time with out the index is 3min. > > I see no way a simple query planner can account for factors like > available ram, disk io speeds and CPU speeds. The solution DRH > suggests is perfect for my needs.
First, CPU speed is probably not an issue here. Knowing the size of the tables and indexes relative to RAM/cache size sure is relevant though. Given knowledge of table row counts, why couldn't SQLite3 recognize that your query is best planned to do a full table scan? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
