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
