title: Sqlite database optimization
body: <p>I have sqlite3 database with three tables:</p>
  
  <pre><code>CREATE TABLE document (
    id Int PRIMARY KEY NOT NULL,
    root_id Int,
    name Varchar(100),
    active Tinyint
  );
  CREATE INDEX IDX_documentId ON document (id);
  CREATE INDEX IDX_documentName ON document (name);
  
  CREATE TABLE dictionary (
    id Int PRIMARY KEY NOT NULL,
    word Varchar(100) NOT NULL
  );
  CREATE INDEX IDX_dictionaryId ON dictionary (id);
  CREATE UNIQUE INDEX IDX_dictionaryWord ON dictionary (word ASC);
  
  CREATE TABLE document_index (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    document_id Int NOT NULL,
    word_id Int NOT NULL,
    FOREIGN KEY(document_id) REFERENCES document(id),
    FOREIGN KEY(word_id) REFERENCES dictionary(id)
  );
  CREATE INDEX IDX_documentIndexId ON document_index (id);
  CREATE INDEX IDX_documentIndexDocId ON document_index (document_id);
  CREATE INDEX IDX_documentIndexWordId ON document_index (word_id);
  </code></pre>
  
  <p>And I have sql script to select all documents wich contains word from the dictionary:</p>
  
  <pre><code>SELECT document.id, document.name
  FROM document
       INNER JOIN document_index on document_index.document_id=document.id
       INNER JOIN dictionary on dictionary.id=document_index.word_id
  WHERE dictionary.word LIKE @pQuery
     AND document.active = 1
     AND document.root_id in (@pRoot1, @pRoot2, @pRoot3, @pRoot4, @pRoot5, @pRoot6, @pRoot7)
  </code></pre>
  
  <p>When the dictionary contains ~= 400,000 records, document ~= 1000 records and document_index ~= 500,000 records, the query is executing about 30 seconds on my iPad 2.</p>
  
  <p>How to optimize query or change the structure of the database (add indexes for example) to reduce the query time?</p>