title: Indices not working on sqlite table
body: <p>I am using indices on columns on which I am making a search. The indices are created like this:</p>
  
  <pre><code>CREATE INDEX index1 on &lt;TABLE&gt;(&lt;col1&gt; COLLATE NOCASE ASC)
  CREATE INDEX index2 on &lt;TABLE&gt;(&lt;col2&gt; COLLATE NOCASE ASC)
  CREATE INDEX index3 on &lt;TABLE&gt;(&lt;col3&gt; COLLATE NOCASE ASC)
  </code></pre>
  
  <p>Now, the select query to search for records is like this:</p>
  
  <pre><code>select &lt;col1&gt; from &lt;TABLE&gt; where &lt;col1&gt; like '%monit%' AND &lt;col2&gt; like '%84%'   GROUP BY &lt;col1&gt; limit 0,501;
  </code></pre>
  
  <p>When I run EXPLAIN QUERY PLAN on my sqlite database like this:</p>
  
  <pre><code>EXPLAIN QUERY PLAN select &lt;col1&gt; from &lt;TABLE&gt; where &lt;col1&gt; like '%monit%' AND &lt;col2&gt; like '%84%'   GROUP BY &lt;col1&gt; limit 0,501;
  </code></pre>
  
  <p>It returns the output as:</p>
  
  <p>0|0|0|SCAN TABLE  USING INDEX  (~250000 rows)</p>
  
  <p>and when I drop the index, the output this EXPLAIN QUERY PLAN produces is:</p>
  
  <p>0|0|0|SCAN TABLE  (~250000 rows)
  0|0|0|USE TEMP B-TREE FOR GROUP BY</p>
  
  <p>Isn't the number of rows that are scanned (~250000 rows) were supposed to be lesser when index was used in searching the table???</p>
  
  <p>I guess the problem here is with LIKE keyword, because I have read somewhere that LIKE keyword nullifies the use if indices... <a href="http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html" rel="nofollow noreferrer">Here is the link</a></p>
  
  <p>EDIT: For indices to work on a query which is using LIKE, The right-hand side of the LIKE must be a string literal that does not begin with a wildcard character. So, in the above query, I tried using search parameter in like without '%' at the beginning:</p>
  
  <pre><code>EXPLAIN QUERY PLAN select &lt;col1&gt; from &lt;TABLE&gt; where &lt;col1&gt; like 'monit%' AND &lt;col2&gt; like '84%'   GROUP BY &lt;col1&gt; limit 0,501;
  </code></pre>
  
  <p>and the output I got was this:</p>
  
  <p>0|0|0|SEARCH TABLE partnumber USING INDEX model_index_partnumber (model>? AND model
  
  <p>so,you see. The number of rows being searched (rather than scan) are (~15625 rows) in this.
  But the problem now is I cannot do away with % wild card at the beginning. Anyone pls suggest me an alternative way to achieve the same....</p>
  
  <p>EDIT:
  I have tried using FTS3 from terminal but when I typed this query:</p>
  
  <pre><code>CREATE VIRTUAL TABLE &lt;tbl&gt; USING FTS3 (&lt;col_list&gt;);
  </code></pre>
  
  <p>Its throwing error as:
  Error: no such module: FTS3</p>
  
  <p>Someone pls help me to enable FTS3 from terminal as well as XCode (need the steps I must perform for both tasks).</p>
  
  <p>I am using sqlcipher and have already perform this from terminal:</p>
  
  <pre><code>CFLAGS="-DSQLITE_ENABLE_FTS3=1" ./configure 
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>Please visit the question <a href="https://stackoverflow.com/questions/9596099/sqlite-table-taking-time-to-fetch-the-records-in-like-query">sqlite table taking time to fetch the records in LIKE query</a> posted by me</p>
  
  <p>EDIT:</p>
  
  <p>Hey All, I got some success. I modified my select query to look like this:</p>
  
  <pre><code>select distinct description collate nocase as description from partnumber where rowid BETWEEN 1 AND (select max(rowid) from partnumber) AND description like '%a%' order by description;
  </code></pre>
  
  <p>And Bingo, the search time was like never before. But the problem now is when I execute the command EXPLAIN QUERY PLAN like this, it shows me using B-Tree for distinct which I dont want to use.</p>
  
  <pre><code>explain query plan select distinct description collate nocase as description from partnumber where rowid BETWEEN 1 AND (select max(rowid) from partnumber) AND description like '%a%' order by description;
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>0|0|0|SEARCH TABLE partnumber USING INTEGER PRIMARY KEY (rowid&gt;? AND rowid&lt;?) (~15625 rows)
  0|0|0|EXECUTE SCALAR SUBQUERY 1
  1|0|0|SEARCH TABLE partnumber USING INTEGER PRIMARY KEY (~1 rows)
  0|0|0|USE TEMP B-TREE FOR DISTINCT
  </code></pre>