SQLITE query performance is too much to be reliable or understandable

Question

SQLITE query performance is too much to be reliable or understandable

Short version of the problem.

Sometimes a query (which uses indexes), which usually runs in a second, takes a minute or more to complete . EXPLAIN indicates that a full table scan has not been performed. The database is large (2 GB, 4.5 million records) and is used exclusively. Re-launching the same query is fast. Until a certain point ...

Long version of the problem.

I have a SQLITE log database:

    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 ON log(kind,computer,id);
    CREATE INDEX idxlog_kind_computer_process_id ON log(kind,computer,process,id);
    CREATE INDEX idxlog_kind_computer_process_who_id ON log(kind,computer,process,who,id);
    CREATE INDEX idxlog_kind_id ON log(kind,id);

=== > 'debug', 'error', 'warn', 'info'
=== >
process === >
=== > ( )

. id ORDER BY.

Sqlite: 3.7.7.1
: Windows XP
: Delphi sqlite3.dll( sqlite.org)
( ):

PRAGMA encoding = "UTF-8"; 
  PRAGMA foreign_keys = ON; 
  PRAGMA synchronous = NORMAL; 
  PRAGMA page_size = 8192; 
  PRAGMA automatic_index = 0;
  PRAGMA temp_store = FILE;

SQLITE:

SQLite3_Open_v2(@UTF8String(AFileName)[1], Fdb,
    SQLITE_OPEN_READWRITE or
    SQLITE_OPEN_CREATE or
    SQLITE_OPEN_PRIVATECACHE or
    SQLITE_OPEN_NOMUTEX
  );

:

SELECT 
   1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log  
WHERE id > 4149245 AND id <= 9223372036854775807 AND kind = 'error' 

UNION ALL

SELECT 
   1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log  
WHERE id > 4149245 AND id <= 9223372036854775807 AND kind = 'debug' 

UNION ALL

 SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
 FROM log  
 WHERE id > 4149245 AND id <= 9223372036854775807 AND kind = 'timing' 

ORDER BY id DESC LIMIT 100

UNION .

~ 4,5 , ~ 1,8 . ( ).

.

100 , "" .

. . :

    SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log _computer_process_id  
WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'error' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log _computer_process_id  
WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'warn' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log _computer_process_id  
WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'info' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log _computer_process_id  
WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'debug' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
FROM log _computer_process_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'timing' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  

ORDER BY id DESC LIMIT 100

plan: 4,0,0, AS _computer_process_id idxlog_kind_computer_process_id ( =? =? =?) (~ 2 ) 5,0,0, AS _computer_process_id idxlog_kind_computer_process_id ( =? =? =?) (~ 2 ) 3,0,0, 4 5 ( ) 6,0,0, AS _computer_process_id idxlog_kind_computer_process_id ( =? =? =?) (~ 2 ) 2,0,0, 3 6 ( ) 7,0,0, AS _computer_process_id idxlog_kind_computer_process_id ( =? =? =?) (~ 2 ) 1,0,0, 2 7 ( ) 8,0,0, AS _computer_process_id idxlog_kind_computer_process_id ( =? =? =?) (~ 2 ) 0,0,0, 1 8 ( )

61326

  SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'error' AND computer='KRAFTWAY'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'warn' AND computer='KRAFTWAY'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'debug' AND computer='KRAFTWAY'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'timing' AND computer='KRAFTWAY'  ORDER BY id DESC LIMIT 100

: 3,0,0, AS _computer_id INDEX idxlog_kind_computer_id ( =? =?) (~ 2 ) 4,0,0, AS_computer_id idxlog_kind_computer_id ( =? =?) (~ 2 ) 2,0,0, 3 4 ( ) 5,0,0, AS _computer_id idxlog_kind_computer_id ( =? =?) (~ 2 ) 1,0,0, 2 5 ( ) 6,0,0, AS_computer_id idxlog_kind_computer_id ( =? =?) (~ 2 ) 0,0,0, 1 6 ( )

45643

     SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_process_who_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'error' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  AND who='main(TMessageRouter)'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_process_who_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'warn' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  AND who='main(TMessageRouter)'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_process_who_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'info' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  AND who='main(TMessageRouter)'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_process_who_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'debug' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  AND who='main(TMessageRouter)'  

UNION ALL 

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id FROM log _computer_process_who_id  WHERE id > 4149246 AND id <= 9223372036854775807 AND kind = 'timing' AND computer='KRAFTWAY'  AND process='D:\xxx.exe'  AND who='main(TMessageRouter)'  

ORDER BY id DESC LIMIT 100

plan: 4,0,0, AS _computer_process_who_id idxlog_kind_computer_process_who_id ( =? =? =? =?) (~ 2 ) 5,0,0, AS _computer_process_who_id idxlog_kind_computer_process_who_id ( =? =? =? =?) (~ 2 ) 3,0,0, 4 5 ( ) 6,0,0, AS_computer_process_who_id idxlog_kind_computer_process_who_id ( =? =? =? =?) (~ 2 ) 2,0,0, 3 6 ( ) 7,0,0, AS _computer_process_who_id INDEX idxlog_kind_computer_process_who_id ( =? =? =? =?) (~ 2 ) 1,0,0, 2 7 ( ) 8,0,0, AS_computer_process_who_id idxlog_kind_computer_process_who_id ( =? =? =? =?) (~ 2 ) 0,0,0, 1 8 ( )

175899

, 3 .

. ? , "EXPLAIN QUERY PLAN" + SQL . SCAN. , FULL TABLE , , .

, , .

/ 1 / (, , , 100 ) . , sqlite.dll, ntdll.ZwReadFile.

. , .

, .

, . - 160 , - 2 .

: .

, , .

. , SQLITE , ? 1 /? SQLITE, ? ? , , , ?

.

UPDATE.

SELECT 1 , id rowid, kind, who, msg, computer, process, created_at, id FROM log WHERE id > 4033214 id <= 9223372036854775807 AND kind = 'error' AND computer = 'KRAFTWAY' AND process = 'D:\xxx.exe'

UNION ALL

SELECT 1 , id rowid, kind, who, msg, computer, process, created_at, id FROM log WHERE id > 4033214 id <= 9223372036854775807 AND kind = 'warn' = 'KRAFTWAY' process = 'D:\xxx.exe'

UNION ALL

SELECT 1 , id rowid, kind, who, msg, computer, process, created_at, id FROM log WHERE id > 4033214 id <= 9223372036854775807 AND kind = 'info' AND computer = 'KRAFTWAY' AND process = 'DD:\xxx.exe'

UNION ALL

SELECT 1 , id rowid, kind, who, msg, computer, process, created_at, id FROM log WHERE id > 4033214 id <= 9223372036854775807 AND kind = 'debug' AND computer = 'KRAFTWAY' AND process = 'D:\xxx.exe'

UNION ALL

SELECT 1 , id rowid, kind, who, msg, computer, process, created_at, id FROM log WHERE id > 4033214 id <= 9223372036854775807 AND kind = 'timing' AND computer = 'KRAFTWAY' AND process = 'D:\xxx.exe'

ORDER BY id DESC LIMIT 100

plan: 4,0,0, SEARCH TABLE log idxlog_kind_computer_process_id_who_msg_created_at ( =? =? =?) (~ 2 ) 5,0,0, idxlog_kind_computer_process_id_who_msg_created_at ( =? =? =?) (~ 2 ) 3,0,0, 4 5 ( ) 6,0,0, idxlog_kind_computer_process_id_who_msg_created_at ( =? =? =?) (~ 2 ) 2,0,0, 3 6 ( ) 7,0,0, idxlog_kind_computer_process_id_who_msg_created_at ( =? =? =?) (~ 2 ) 1,0,0, 2 7 ( ) 8,0,0, idxlog_kind_computer_process_id_who_msg_created_at ( =? =? =?) (~ 2 ) 0,0,0, 1 8 ( )

1138669 ~ 18 .

: 100 .

18 db 500 /.

+5

performance database sqlite windows-xp

Gregory 19 . '11 12:12

2

zeFrenchy · Answer 1 · 2011-09-22T14:54:58+0000

,

PRAGMA integrity_check

?

PRAGMA locking_mode = EXCLUSIVE

, ?

Benoit · Answer 2 · 2011-09-22T15:10:48+0000

:

CREATE INDEX i_informations ON log (computer ASC, process ASC, id DESC);

SELECT 1 as today, id as rowid, kind,who,msg,computer,process,created_at,id 
  FROM log _computer_process_id  
 WHERE id BETWEEN 4149247 AND 9223372036854775807
   AND kind IN ('info','debug','timing')
   AND computer='KRAFTWAY'
   AND process='D:\xxx.exe'  
 ORDER BY id DESC limit 100

SQLITE query performance is too much to be reliable or understandable

More articles: