I have a log's database. Schema :
CREATE TABLE log(id integer primary key autoincrement,msg text,created_at int,kind text,computer text,process text,who text); CREATE INDEX idxlog_created_at ON log(created_at); CREATE INDEX idxlog_kind_computer_id_process_who_msg_created_at ON log(kind,computer,id desc,process,who,msg,created_at); CREATE INDEX idxlog_kind_computer_process_id_who_msg_created_at ON log(kind,computer,process,id desc,who,msg,created_at); CREATE INDEX idxlog_kind_computer_process_who_id_msg_created_at ON log(kind,computer,process,who,id desc,msg,created_at); CREATE INDEX idxlog_kind_id_computer_process_who_msg_created_at ON log(kind,id desc,computer,process,who,msg,created_at); Database: ~ 3.5 Gb size, ~ 2.8 mln records. page_size=8192 bytes, UTF-8. Platform: Windows XP, Intel Core Duo 3 Ghz. SQLITE: sqlite3.exe console, latest version (3.7.7.1). Query: SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log WHERE id > 4070636 AND id <= 9223372036854775807 AND kind = 'info' AND computer='KRAFTWAY'; Query plan (EXPLAIN QUERY PLAN): 0|0|0|SEARCH TABLE log USING COVERING INDEX idxlog_kind_computer_process_who_id_msg_created_at (kind=? AND computer=?) (~2 rows) There are no records in database that match query, i.e. query returns nothing. Now, the problem. Reboot system. Run query. Elapsed: ~100 SECONDS. Re-run query. Now it executes in less than a second. Re-run query. The same: less than a second. Tried it several times. RUNNING THIS QUERY JUST AFTER REBOOT TAKES UNPREDICTABLE LARGE TIME, FROM 1 TO 2 MINUTES. Is this a sqlite bug or normal behaviour? If it's normal, there are some questions. Am I hitting some limit with my database? Which one? Why is SQLITE (having nicely matching index) is spending so much time to execute? SQLITE has to cache some index data? Yeah but the console process (during execution of this query) is reading at about 800 Kb per second rate! (while HDD can make 100 Mb per second) Why so serious? Execuse me, why so slow? And SQLITE process it's not consuming CPU (less than 1 percent). What is SQLITE doing during this long running query? It's own private memory is holding still at about 20 Mb. It's not consuming CPU and HDD. What is he waiting for? This problem makes SQLITE completely unusable in my project. I've shared database and scripts (Warning: it's RAR archive, ZIP cannot hold files of 3.5 Gb size. You can download WINRAR here: http://www.rarlab.com/download.htm ): http://dl.dropbox.com/u/2168777/deadly_sqlite.rar Download size: ~150 Mb. Unzip and execute "run.cmd". You'll see execution plan and after a while there will be line "Seconds elapsed: XXX". _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
