Simon Slavin schrieb am 24.08.2011 23:33: > On 24 Aug 2011, at 9:59pm, GB wrote: > >> Simon Slavin schrieb am 24.08.2011 22:38: >> >>> SELECT itemID FROM t WHERE itemID>= 1000000 AND itemID<= 2000000 AND >>> createdAt>= '2011-08-01' createdAt<= '2011-08-02' >> Thank you for your thoughts but I already tried this with no different >> results than before. And according to >> http://www.sqlite.org/optoverview.html#between_opt this is exactly what >> happens behind the curtains when SQLite processes BETWEEN statements. > Just checking. > >> What I'm actually looking for is a way to make SQLite create and >> consider histogram data for rowid lookup the same way as for regular >> indexes. > SQLite should be comparing the 'chunkiness' of the itemID and createdAt > columns. It won't know how chunky they are around the specific values > specified in that particular SELECT command. It also won't be able to > compare the distance between 10000000 and 2000000, a million values, and > '2011-08-01' and '2011-08-02', which is just two values. The 'two values' > would suggest that using an index on that column might be the best way to > start, but I don't think the query optimizer can actually tell that. > > But I don't know details about how the optimizer uses its information so I > can't tell for sure whether it's making a good guess or it could be improved. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Well, that is exactly what the sqlite_stat2 table is meant for. It's information is supposed to make the query planner able to decide upon the usefulness of an index. Unfortunately, histogram information is not collected for the implicit rowid index by the ANALYZE command, so the planner has to rely on some rule of thumb which in turn seems to favour the rowid index. And if I make the itemID Column a regular (non-INTEGER PRIMARY KEY) one, the query plan gets generated as expected using the index on createdAt.
regards gerd _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
