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?
Still. There's an index: idxlog_kind_computer_id_process_who_msg_created_at ( kind, computer, id, ... ) Query is kind = XXX AND computer = YYY and id BETWEEN ZZZ1 and ZZZ2 To my opinion this information is sufficient to jump directly to index position XXX,YYY,ZZZ1. SQLITE must be able to do this real fast, isn't it? 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? Does SQLITE needs all of index to be in RAM to begin using it? If so it can be the reason of bad performance... 22 июля 2011, 17:07 от Max Vlasov <max.vla...@gmail.com>: > On Fri, Jul 22, 2011 at 3:50 PM, Григорий Григоренко <grigore...@mail.ru> > wrote: > > Okay, I rebooted and tested again. > > > > First run took 76 seconds. Read bytes: ~ 307 Mb, Write bytes: ~ 66 Kb. > > > > Re-running: 1 second, Read bytes: ~ 307 Mb, Write bytes ~ 66 Kb. > > > > > > Grigory, you posted to me directly (without the list e-mail), I'm > reposting this reply to the list > > Ok, I downloaded the db and it actually takes long time to execute. > But this can be explained. Actually if you change your fist id > to > zero, the query starts to return actual results, so there are many > records with kind='info' and computer = 'KRAFTWAY' in your database. > So sqlite really uses the index for locating your records. But the > problem is also that your db is very fragmented so sqlite when reading > the index actually reads different parts of this file and it makes the > windows cache system mad. I think vacuum command should help. > > Max > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
