On Tue, Jul 26, 2011 at 2:18 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> > On 26 Jul 2011, at 11:05am, Григорий Григоренко wrote: > > > This leads us to conclusion: index in SQLITE database if scattered and > cannot be "jumped directly" to N-th element. SQLITE has to read it somehow > consecutively. > > > > And so SQLITE has to read half of index (!) to find matching index > record. > > I don't think it's SQLite itself that's reading half the index. I think > it's some part of your operating system that's trying to cache all of your > database file as SQLite reads lots of different parts of it spread about > randomly. Unfortunately I don't see how an OS can reasonably do that since > it will produce the slow speeds you're complaining about. > > Simon, actually I narrowed down the problem (cmiiw). The simple table CREATE TABLE [abctable] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c) add many records (100,000) INSERT INTO abctable (a, b, c) VALUES (10, 20, 30) Good variant CREATE INDEX idxabc ON abctable (a, b, c) SELECT * FROM abctable WHERE a=10 and b=20 and c > 1000000 Sqlite reads few data (3k actually for 100,000 records) to show empty result Another variant CREATE INDEX idxabid ON abctable (a, b, id) SELECT * FROM abctable WHERE a=10 and b=20 and id > 1000000 Sqlite reads much (1,7MB) Checked with 3.7.6.2, I suppose the latter due to some special meaning of the id/rowid, but I suppose the second case should work with fewer reads Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
