title: SQLite - how to return rows containing a text field that contains one or more strings?
body: <p>I need to query a table in an SQLite database to return all the rows in a table that match a given set of words. </p>
  
  <p>To be more precise: I have a database with ~80,000 records in it.  One of the fields is a text field with around 100-200 words per record.  What I want to be able to do is take a list of 200 single word keywords {"apple", "orange", "pear", ... } and retrieve a set of all the records in the table that contain at least one of the keyword terms in the description column.</p>
  
  <p>The immediately obvious way to do this is with something like this:</p>
  
  <pre><code>SELECT stuff FROM table 
  WHERE (description LIKE '% apple %') or (description LIKE '% orange %') or ... 
  </code></pre>
  
  <p>If I have 200 terms, I end up with a big and nasty looking SQL statement that seems to me to be clumsy, smacks of bad practice, and not surprisingly takes a long time to process - more than a second per 1000 records. </p>
  
  <p>This answer <a href="https://stackoverflow.com/questions/1989990/better-performance-for-sqlite-select-statement">Better performance for SQLite Select Statement</a> seemed close to what I need, and as a result I created an index, but according to <a href="http://www.sqlite.org/optoverview.html" rel="nofollow noreferrer">http://www.sqlite.org/optoverview.html</a> sqlite doesn't use any optimisations if the LIKE operator is used with a beginning % wildcard.</p>
  
  <p>Not being an SQL expert, I am assuming I'm doing this the dumb way.  I was wondering if someone with more experience could suggest a more sensible and perhaps more efficient way of doing this?</p>
  
  <p>Alternatively, is there a better approach I could use to the problem?</p>