I need to query a table in a SQLite database to return all rows in a table that match a given set of words.
More precisely: I have a database with ~ 80,000 records. One of the fields is a text field with 100-200 words per entry. What I want to do is take a list of 200 words with one word {"apple", "orange", "pear", ...} and get a set of all the entries in the table that contain at least one key term words in the description column.
The obvious way to do this is, for example, the following:
SELECT stuff FROM table
WHERE (description LIKE '% apple %') or (description LIKE '% orange %') or ...
If I have 200 terms, I get a big and unpleasant look at the SQL query, which seems clumsy to me, unpleasant due to bad practice, and it is not surprising that it takes a lot of time to process - more than a second per 1000 records,
This answer The best performance for SQLite Select Statement seems close to what I need, and as a result I created an index, but according to http://www.sqlite.org/optoverview.html sqlite does not use any optimizations if the LIKE statement used with start% pattern.
Not being an SQL expert, I guess I'm doing it stupidly. I was wondering if someone with more experience could suggest a more reasonable and possibly more efficient way to do this?
, , ?