title: SQLite Database taking too much time to retrieve (or fetch some data)
body: <p>I have a sqlite cipher database with around 80000 records (containing 7 columns). Its currently taking too much time in searching the records. I need an optimized way of searching for 1 or more record based on some criteria (need something like indexing) so that I can reduce the searching time. Does anybody has any idea about how to proceed to achieve this? Need help urgently. :(</p>
  
  <p>EDITED:
  I am using sqlite cipher API because I need to store the encrypted data in sqlite database. I have set the pragma key and the encryption being used is AES - 256. I have also created the index on each table in my database. But my searching is not fast. Its taking too much time. Need to solve this ASAP.</p>
  
  <p>2nd TIME EDITED:
  Hey guys, I am badly stuck this time. I am making a search as soon as user clicks on a text field. To summarize the problem, let me tell you the flow.</p>
  
  <p>I have 2 text fields and a table containing ~80k records and 7 columns.
  When I type anything in the field1, a search is made onto the table with the content of the field1. So lets say I type 'a' in my field1, then a select query returns all the records containing 'a' letter in column1. So, the table is being searched as soon as I type the letters.
  Uptill now there is no problem. Its returning the records without any time delay. The query is:</p>
  
  <pre><code>SELECT DISTINCT &lt;COL1&gt; COLLATE NOCASE as &lt;COL1&gt; from &lt;TABLE_NAME&gt; where &lt;COL1&gt; like '%ab%'   LIMIT 0,501
  </code></pre>
  
  <p>Now, if I click on field 2, then I see the cursor on field2 after 1 secs as this query gets execute:</p>
  
  <pre><code>SELECT DISTINCT &lt;COL2&gt; COLLATE NOCASE as &lt;COL2&gt; from &lt;TABLE_NAME&gt; where &lt;COL1&gt; like '%ab%'   LIMIT 0,501
  </code></pre>
  
  <p>Now, the db fetching process is taking time if I type anything on field2. Here is the final query:</p>
  
  <pre><code>SELECT DISTINCT &lt;COL2&gt; COLLATE NOCASE as &lt;COL2&gt; from &lt;TABLE_NAME&gt; where &lt;COL1&gt; like '%ab%' AND &lt;COL2&gt; like '%cd%'  LIMIT 0,501
  </code></pre>
  
  <p>When I tried to analyse the problem, I came to know that the problem is due to DISTINCT Keyword.
  I have created indices on the columns as follows:</p>
  
  <pre><code>CREATE INDEX &lt;name1&gt; on &lt;TABLE_NAME&gt;(&lt;COL1&gt;)
  CREATE INDEX &lt;name2&gt; on &lt;TABLE_NAME&gt;(&lt;COL1&gt; COLLATE CASE)
  CREATE INDEX &lt;name3&gt; on &lt;TABLE_NAME&gt;(&lt;COL2&gt;)
  CREATE INDEX &lt;name4&gt; on &lt;TABLE_NAME&gt;(&lt;COL2&gt; COLLATE CASE)
  </code></pre>
  
  <p>Anyone has any idea how to make this retrieval fast.</p>