> > #1 I take it your query is CPU bound the first time? Hmm, not sure. CPU was still under 1 % usage.
> > #2 Can you show us the query planner please? > #3 Can you show us the query planner minus the "INDEXED BY"? Sure. Executing: SELECT * FROM log INDEXED BY idxlog_kind_computer WHERE kind = 'info' AND computer=1 and id > 7070636 LIMIT 100; Query plan: 0|0|0|SEARCH TABLE log USING INDEX idxlog_kind_computer (kind=? AND computer=?) (~3 rows) Executing: SELECT * FROM log WHERE kind = 'info' AND computer=1 and id > 7070636 LIMIT 100; Query plan: 0|0|0|SEARCH TABLE log USING INDEX idxlog_kind_computer_process_who (kind=? AND computer=?) (~3 rows) Query planner has selected idxlog_kind_computer_process_who index. > > > #4 Can you show us sqlite_stat1? Actually, there isn't any. I've not ran ANALYZE on this database. Since I'm using INDEXED BY there's no need in query planner decisions. > > #5 Can you show us your tables now? > You mean db schema? // table with computer, process and who values CREATE TABLE item(name text,value text); CREATE UNIQUE INDEX idxitem_name_value ON item(name,value); // log-table CREATE TABLE log(id integer primary key autoincrement,msg text,created_at int,kind,computer,process,who); CREATE INDEX idxlog_created_at ON log(created_at); CREATE INDEX idxlog_kind ON log(kind); CREATE INDEX idxlog_kind_computer ON log(kind,computer); CREATE INDEX idxlog_kind_computer_process ON log(kind,computer,process); CREATE INDEX idxlog_kind_computer_process_who ON log(kind,computer,process,who); Since I've normalized data selecting (in real-life application) looks like: SELECT ... kind,msg,c.value as computer,p.value as process,w.value as who,created_at,id FROM log ... LEFT JOIN item c ON c.rowid=log.computer LEFT JOIN item p ON p.rowid=log.process LEFT JOIN item w ON w.rowid=log.who ... > #6 What happens if you do "pragma cache_size=15000"? I'd make cache_size = > (readbytes/8192)*1.1 at least. (readbytes/8192)*1.1 gives us ~ 6700 pages. Tried "pragma cache_size=15000;" and nothing changed. Sqlite3 still has read ~ 50 Mb and spent 27 seconds on first query run. > > #7 Care to post your database again? > http://dl.dropbox.com/u/2168777/db2.rar > > > It still seems to me this should run faster the first time unless you have a > really slow disk system or sqlite is doing something silly (which I doubt). > > Perhaps the cache being to small is hurting things. I'm not sure cache settings are important. You see, I've tried running query with different cache_size. Even with cache_size=100. Tried cache_size from 100 to 25000 and monitored memory usage of console process to make sure it was using only the amount of memory given by cache. Timing is _always_ ~ 1 second on subsequent (not first) runs. You can try it yourself with attached data. > > > > 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 3:30 AM > To: sqlite-users > Subject: EXT :Re: [sqlite]SELECT query first run is VERY slow > > Okay, here are some steps I've done. > > 1) normalize db; > > I've created single table (item). "computer","process" and "who" fields in > log table became rowid integers pointing to this table. > "kind" is still a short string. > > 2) give up covering indexes; > > For those not aware (<thismessage:/>http://www.sqlite.org/queryplanner.html , > 1.7 Covering Indices) it's an index that has additional columns at it's end ( > being selected by query). > Covering index eliminate the need to read data from db records. But it > increases size of index and size of database. > > 3) use INDEXED BY to suggest index to query planner. > > Since indexes are created exactly for specific query. > > > Software was working during weekend. > > Yesterday's database has 6 mln records and it's only 1.1 Gb in size. > > Comparing to 4 mln records and 3.5 Gb size before. > > Now, the long running query took 27 seconds and it has read 50 Mb from > database (compare to 2 minutes and 307 Mb before). > > _______________________________________________ > 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
