> Could you post your timings and read stats again? Are you happy with what > you're seeing now? >
Actually, there was ~ 50% speed-up. More or less. The idea of normalizing worked great, thank you. I'm concerned about SQLITE indexes. > > > I think when you drop the index and recreate it still scatters. But re-doing > your data with the new kind will tell for sure. I take it you have to build > the index while running and can't wait until it's done. To me problem is narrowed to the fact that SQLITE is ignoring ROWID value while scanning index. Using "INDEX ON Foo(Bar)" and condtion like "Bar = XXX and Rowid > YYY" SQLITE reads too much. Looks like in this case SQLITE reads all of index records matching condition Bar = XXX and scan them. And if Bar has only a few allowed values (= bad selectivity ) this takes lot's of time and disk i/o cause there are lots of records with the same Bar value. Looks like SQLITE is not using knowledge that index records are sorted by Bar and then by Rowid. This is something I don't (and didn't) expect... > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
