Sqlite database optimization

Question

I have sqlite3 database with three tables:

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);

And I have sql script to select all documents wich contains word from the dictionary:

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)

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.

How to optimize query or change the structure of the database (add indexes for example) to reduce the query time?

Answer 1

I found the solution. This solution increased the speed of the query execution in 60! times. I found it here and more detailed - here. It's simple, I replaced LIKE expression to compare >= and <:

old:

dictionary.word LIKE 'prezident%'

new:

dictionary.word >= 'prezident' AND dictionary.word < 'prezidentz' /* Added z to the second string*/ 

This solution has one limitation, I can look for by a part of the string, but at the end of the string ony, i.e. 'expr%'.

Thanks all for the help!