On Fri, Jul 22, 2011 at 5:25 PM, Григорий Григоренко <grigore...@mail.ru> wrote: > Database is "insert-only". There wasn't any deletes or updates, will VACUUM > actually help in this case? I though it was about unused space? >
There's also internal fragmentation coming from the nature of indexes. If your inserted data have to be placed in the middle of the index and this allocates a new page at the end of the file then the logical order of the pages used for this index will be non-sequential in terms of hard disc access. > > And after finding this position inside index SQLITE clearly sees that > previous and next record does not match query, so query should return nothing. > > This cannot take long. It's like scanning B-TREE to a predefined position and > then reading just 2 records near. > > And that's all. Why spending 2 minutes? You query contained the id range not existed in the db so maybe here can be some optimization improvement. That's why probably Richard asked you to send the analyzed data. But as I suppose you're not going to supply non existing id ranges :) so if we change the id range to a valid one, the data will contain many records and sqlite should read the index that is internally fragmented due to the reasons explain above. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
