You need to normalize your "kind" value.
.pragma cache_size=15000; drop index idxlog_kind_computer; create table kind(id integer,kind text); insert into kind values(1,'debug'); insert into kind values(2,'error'); insert into kind values(3,'info'); insert into kind values(4,'timing'); insert into kind values(5,'warn'); update log set kind=1 where kind='debug'; update log set kind=2 where kind='error'; update log set kind=3 where kind='info'; update log set kind=4 where kind='timing'; update log set kind=5 where kind='warn'; create index idxlog_kind_computer ON log(kind,computer); Then see how long your first query takes. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Григорий Григоренко [grigore...@mail.ru] Sent: Monday, July 25, 2011 8:45 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow I think I narrowed the problem a bit. Guys, hope I'm not bothering you too much :) I've calculated size of index (it is index on log (kind,computer) ) of its own: dropped index, run VACUUM and re-created index. Database file increased by 105 Mb (and sqlite3 process counter shows that there were ~105 Mb written to disk). This means that index on log(kind, computer) takes 105 Mb of database file (and whole size of database is 1259 Mb). Now, I'm running query which is using this index (and is not returning any data) and monitor that sqlite3 process reads ~50 Mb. So there are two major problems here. 1) SQLITE has to read about _half of index_ before it can use it (and understand there are no records matching query). If cache is enough to hold 50 Mb then on subsuquent queries sqlite process is not reading at all. 2) SQLITE is reading abnormally slowly during this first-time running query (waiting for something a lot?). During index creation I monitored sqlite3 process and it was consuming CPU at ~20% rate and it's doing I/O at ~10 Mb per second rate. That's what I call "normal load"! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
