title: SQLite Slow Select Query
body: <p>I'm running the following Select query:</p>
  
  <pre><code>SELECT "entry"."id" AS "entry_id",
  "entry"."input" AS "entry_input",
  "entry"."output" AS "entry_output",
  "entry"."numOfWords" AS "entry_numOfWords",
  "entry"."times_seen" AS "entry_times_seen",
  "word_class"."value" AS "word_class_value",
  "dominant_noun"."noun" AS "dominant_noun_noun",
  "dominant_noun"."article" AS "dominant_noun_article",
  "dominant_noun"."isPluaral" AS "dominant_noun_isPluaral",
  "subject"."subjectIndex" AS "subject_subjectIndex",
  "last_time_visited"."value" AS "last_time_visited_value"
  FROM "entry" "entry"
  LEFT JOIN "word_class" "word_class" ON "word_class"."entryId"="entry"."id"
  LEFT JOIN "dominant_noun" "dominant_noun" ON "dominant_noun"."entryId"="entry"."id"
  LEFT JOIN "subject_entries_entry" "subject_entry" ON "subject_entry"."entryId"="entry"."id"
  LEFT JOIN "subject" "subject" ON "subject"."id"="subject_entry"."subjectId"
  LEFT JOIN "last_time_visited" "last_time_visited" ON "last_time_visited"."entryId"="entry"."id"
  WHERE "entry"."inputLang" = 31
  AND ("entry"."input" like '% hilfe %' OR "entry"."input" like 'hilfe %' OR "entry"."input" like '% hilfe')
  ORDER BY "word_class"."value" DESC, "entry"."numOfWords" ASC;
  </code></pre>
  
  <p>Time result:</p>
  
  <pre><code>real    0m15.100s
  user    0m14.072s
  sys     0m1.024s
  </code></pre>
  
  <p>Against this Database schema:</p>
  
  <pre><code>CREATE TABLE sqlite_sequence(name,seq);
  CREATE TABLE IF NOT EXISTS "subject" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "subjectIndex" tinyint NOT NULL);
  CREATE TABLE IF NOT EXISTS "entry" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "inputLang" tinyint NOT NULL, "outputLang" tinyint NOT NULL, "input"
  varchar NOT NULL, "output" varchar NOT NULL, "numOfWords" tinyint NOT NULL, "times_seen" integer NOT NULL DEFAULT (0));
  CREATE TABLE IF NOT EXISTS "abbr" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "value" varchar NOT NULL, "entryId" integer, CONSTRAINT "REL_ca935aaf7
  66cba1e7bfbe90275" UNIQUE ("entryId"), CONSTRAINT "FK_ca935aaf766cba1e7bfbe902757" FOREIGN KEY ("entryId") REFERENCES "entry" ("id"));
  CREATE TABLE IF NOT EXISTS "word_class" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "value" integer NOT NULL, "entryId" integer, CONSTRAINT "REL_941
  45442deb2b2209bd943a787" UNIQUE ("entryId"), CONSTRAINT "FK_94145442deb2b2209bd943a7874" FOREIGN KEY ("entryId") REFERENCES "entry" ("id"));
  CREATE TABLE IF NOT EXISTS "dominant_noun" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "noun" varchar NOT NULL, "article" tinyint NOT NULL, "isPluar
  al" boolean NOT NULL, "entryId" integer, CONSTRAINT "REL_f493eeedea653d8a89f595c82c" UNIQUE ("entryId"), CONSTRAINT "FK_f493eeedea653d8a89f595c82c4" FOREI
  GN KEY ("entryId") REFERENCES "entry" ("id"));
  CREATE TABLE IF NOT EXISTS "last_time_visited" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "value" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP), "e
  ntryId" integer, CONSTRAINT "REL_e631a6f55d59214f8e6aaa6447" UNIQUE ("entryId"), CONSTRAINT "FK_e631a6f55d59214f8e6aaa64478" FOREIGN KEY ("entryId") REFER
  ENCES "entry" ("id"));
  CREATE TABLE IF NOT EXISTS "subject_entries_entry" ("subjectId" integer NOT NULL, "entryId" integer NOT NULL, CONSTRAINT "FK_d2eaa7a84a7963ed94e472cef0b"FOREIGN KEY ("subjectId") REFERENCES "subject" ("id") ON DELETE CASCADE, CONSTRAINT "FK_5f940450dd4c681a9fecf0b14b2" FOREIGN KEY ("entryId") REFERENCES "entry" ("id") ON DELETE CASCADE, PRIMARY KEY ("subjectId", "entryId"));
  CREATE INDEX "IDX_3091789786b922bee00bbb44b1" ON "entry" ("inputLang") ;
  CREATE INDEX "IDX_36ab3550b9e3ef647d1230affc" ON "entry" ("outputLang") ;
  CREATE INDEX "IDX_1b0f6266dffb9a7e6343e7faa4" ON "entry" ("input") ;
  CREATE INDEX "IDX_a77c7936ea412ec1958007154a" ON "entry" ("numOfWords") ;
  CREATE INDEX "IDX_b32699a03d36223ff9bad94ea6" ON "entry" ("times_seen") ;
  </code></pre>
  
  <p>Explain result:</p>
  
  <pre><code>addr  opcode         p1    p2    p3    p4             p5  comment
  ----  -------------  ----  ----  ----  -------------  --  -------------
  0     Init           0     109   0                    00  Start at 109
  1     SorterOpen     6     14    0     k(2,-B,B)      00
  2     OpenRead       0     12    0     7              00  root=12 iDb=0; entry
  3     OpenRead       1     2     0     3              00  root=2 iDb=0; word_class
  4     OpenRead       7     3     0     k(2,,)         02  root=3 iDb=0; sqlite_autoindex_word_class_1
  5     OpenRead       2     5     0     5              00  root=5 iDb=0; dominant_noun
  6     OpenRead       8     6     0     k(2,,)         02  root=6 iDb=0; sqlite_autoindex_dominant_noun_1
  7     OpenRead       3     10    0     2              00  root=10 iDb=0; subject_entries_entry
  8     OpenRead       4     9     0     2              00  root=9 iDb=0; subject
  9     OpenRead       5     7     0     3              00  root=7 iDb=0; last_time_visited
  10    OpenRead       9     8     0     k(2,,)         02  root=8 iDb=0; sqlite_autoindex_last_time_visited_1
  11    Rewind         0     92    0                    00
  12      Column         0     1     1                    00  r[1]=entry.inputLang
  13      Ne             2     91    1     (BINARY)       54  if r[1]!=r[2] goto 91
  14      Column         0     3     4                    00  r[4]=entry.input
  15      Function0      1     3     1     like(2)        02  r[1]=func(r[3..4])
  16      If             1     23    0                    00
  17      Column         0     3     6                    00  r[6]=entry.input
  18      Function0      1     5     1     like(2)        02  r[1]=func(r[5..6])
  19      If             1     23    0                    00
  20      Column         0     3     8                    00  r[8]=entry.input
  21      Function0      1     7     1     like(2)        02  r[1]=func(r[7..8])
  22      IfNot          1     91    1                    00
  23      Integer        0     9     0                    00  r[9]=0; init LEFT JOIN no-match flag
  24      Rowid          0     10    0                    00  r[10]=rowid
  25      SeekGE         7     87    10    1              00  key=r[10]
  26      IdxGT          7     87    10    1              00  key=r[10]
  27      DeferredSeek   7     0     1                    00  Move 1 to 7.rowid if needed
  28      Integer        1     9     0                    00  r[9]=1; record LEFT JOIN hit
  29      Integer        0     11    0                    00  r[11]=0; init LEFT JOIN no-match flag
  30      Rowid          0     12    0                    00  r[12]=rowid
  31      SeekGE         8     83    12    1              00  key=r[12]
  32      IdxGT          8     83    12    1              00  key=r[12]
  33      DeferredSeek   8     0     2                    00  Move 2 to 8.rowid if needed
  34      Integer        1     11    0                    00  r[11]=1; record LEFT JOIN hit
  35      Once           0     44    0                    00
  36      OpenAutoindex  10    3     0     k(3,B,,)       00  nColumn=3; for subject_entries_entry
  37      Rewind         3     44    0                    00
  38        Column         3     1     13                   00  r[13]=subject_entries_entry.entryId
  39        Column         3     0     14                   00  r[14]=subject_entries_entry.subjectId
  40        Rowid          3     15    0                    00  r[15]=rowid
  41        MakeRecord     13    3     1                    00  r[1]=mkrec(r[13..15])
  42        IdxInsert      10    1     0                    10  key=r[1]
  43      Next           3     38    0                    03
  44      Integer        0     16    0                    00  r[16]=0; init LEFT JOIN no-match flag
  45      Rowid          0     17    0                    00  r[17]=rowid
  46      SeekGE         10    80    17    1              00  key=r[17]
  47        IdxGT          10    80    17    1              00  key=r[17]
  48        Integer        1     16    0                    00  r[16]=1; record LEFT JOIN hit
  49        Integer        0     18    0                    00  r[18]=0; init LEFT JOIN no-match flag
  50        Column         10    1     19                   00  r[19]=subject_entries_entry.subjectId
  51        SeekRowid      4     76    19                   00  intkey=r[19]
  52        Integer        1     18    0                    00  r[18]=1; record LEFT JOIN hit
  53        Integer        0     20    0                    00  r[20]=0; init LEFT JOIN no-match flag
  54        Rowid          0     21    0                    00  r[21]=rowid
  55        SeekGE         9     72    21    1              00  key=r[21]
  56        IdxGT          9     72    21    1              00  key=r[21]
  57        DeferredSeek   9     0     5                    00  Move 5 to 9.rowid if needed
  58        Integer        1     20    0                    00  r[20]=1; record LEFT JOIN hit
  59        Rowid          0     24    0                    00  r[24]=rowid
  60        Column         0     3     25                   00  r[25]=entry.input
  61        Column         0     4     26                   00  r[26]=entry.output
  62        Column         0     6     27    0              00  r[27]=entry.times_seen
  63        Column         2     1     28                   00  r[28]=dominant_noun.noun
  64        Column         2     2     29                   00  r[29]=dominant_noun.article
  65        Column         2     3     30                   00  r[30]=dominant_noun.isPluaral
  66        Column         4     1     31                   00  r[31]=subject.subjectIndex
  67        Column         5     1     32                   00  r[32]=last_time_visited.value
  68        Column         1     1     22                   00  r[22]=word_class.value
  69        Column         0     5     23                   00  r[23]=entry.numOfWords
  70        MakeRecord     22    11    35                   00  r[35]=mkrec(r[22..32])
  71        SorterInsert   6     35    22    11             00  key=r[35]
  72        IfPos          20    76    0                    00  if r[20]&gt;0 then r[20]-=0, goto 76
  73        NullRow        5     0     0                    00
  74        NullRow        9     0     0                    00
  75        Goto           0     58    0                    00
  76        IfPos          18    79    0                    00  if r[18]&gt;0 then r[18]-=0, goto 79
  77        NullRow        4     0     0                    00
  78        Goto           0     52    0                    00
  79      Next           10    47    0                    00
  80      IfPos          16    83    0                    00  if r[16]&gt;0 then r[16]-=0, goto 83
  81      NullRow        10    0     0                    00
  82      Goto           0     48    0                    00
  83      IfPos          11    87    0                    00  if r[11]&gt;0 then r[11]-=0, goto 87
  84      NullRow        2     0     0                    00
  85      NullRow        8     0     0                    00
  86      Goto           0     34    0                    00
  87      IfPos          9     91    0                    00  if r[9]&gt;0 then r[9]-=0, goto 91
  88      NullRow        1     0     0                    00
  89      NullRow        7     0     0                    00
  90      Goto           0     28    0                    00
  91    Next           0     12    0                    01
  92    OpenPseudo     11    36    14                   00  14 columns in r[36]
  93    SorterSort     6     108   0                    00
  94      SorterData     6     36    11                   00  r[36]=data
  95      Column         11    10    34                   00  r[34]=last_time_visited_value
  96      Column         11    9     33                   00  r[33]=subject_subjectIndex
  97      Column         11    8     32                   00  r[32]=dominant_noun_isPluaral
  98      Column         11    7     31                   00  r[31]=dominant_noun_article
  99      Column         11    6     30                   00  r[30]=dominant_noun_noun
  100     Column         11    0     29                   00  r[29]=word_class_value
  101     Column         11    5     28                   00  r[28]=entry_times_seen
  102     Column         11    1     27                   00  r[27]=entry_numOfWords
  103     Column         11    4     26                   00  r[26]=entry_output
  104     Column         11    3     25                   00  r[25]=entry_input
  105     Column         11    2     24                   00  r[24]=entry_id
  106     ResultRow      24    11    0                    00  output=r[24..34]
  107   SorterNext     6     94    0                    00
  108   Halt           0     0     0                    00
  109   Transaction    0     0     348   0              01  usesStmtJournal=0
  110   Integer        31    2     0                    00  r[2]=31
  111   String8        0     3     0     % hilfe %      00  r[3]='% hilfe %'
  112   String8        0     5     0     hilfe %        00  r[5]='hilfe %'
  113   String8        0     7     0     % hilfe        00  r[7]='% hilfe'
  114   Goto           0     1     0                    00
  </code></pre>
  
  <p>Explain Query Plan output:</p>
  
  <pre><code>QUERY PLAN
  |--SCAN TABLE entry AS entry
  |--SEARCH TABLE word_class AS word_class USING INDEX sqlite_autoindex_word_class_1 (entryId=?)
  |--SEARCH TABLE dominant_noun AS dominant_noun USING INDEX sqlite_autoindex_dominant_noun_1 (entryId=?)
  |--SEARCH TABLE subject_entries_entry AS subject_entry USING AUTOMATIC COVERING INDEX (entryId=?)
  |--SEARCH TABLE subject AS subject USING INTEGER PRIMARY KEY (rowid=?)
  |--SEARCH TABLE last_time_visited AS last_time_visited USING INDEX sqlite_autoindex_last_time_visited_1 (entryId=?)
  `--USE TEMP B-TREE FOR ORDER BY
  </code></pre>
  
  <p>Analyze output:</p>
  
  <pre><code>subject||1437631
  entry|IDX_b32699a03d36223ff9bad94ea6|2348382 2348382
  entry|IDX_a77c7936ea412ec1958007154a|2348382 67097
  entry|IDX_1b0f6266dffb9a7e6343e7faa4|2348382 2
  entry|IDX_36ab3550b9e3ef647d1230affc|2348382 1174191
  entry|IDX_3091789786b922bee00bbb44b1|2348382 1174191
  abbr|sqlite_autoindex_abbr_1|42575 1
  dominant_noun|sqlite_autoindex_dominant_noun_1|823071 1
  word_class|sqlite_autoindex_word_class_1|2005516 1
  subject_entries_entry|sqlite_autoindex_subject_entries_entry_1|1437631 1 1
  </code></pre>
  
  <p>It often takes more than 10 seconds to get the results. Although this is my first time working with SQLite but 20 seconds reply time seems strange. Please add a comment if i should provide extra info in order to resolve the problem? </p>