You need to normalize your database.
Though it's easy to put everything in one table it's horrendously inefficient for indexing. Your indexes are huge as you're putting strings in them. Ideally you should never have a string in an index if you can avoid it. Too much space, too long a time to index, to long a time to compare. So for starters create a "kind" table and "computer" table and just put the _rowid into your table instead of the string. I'll bet you things will run a lot faster and your database will shrink. Same goes for other strings. 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: Friday, July 22, 2011 5:38 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] SELECT query first run is VERY slow 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
