Searching in multiple columns using Full Text Search(FTS) with multiple tokens using OR operator
SQLite's FTS supports only simple prefix searches.
A query like prod_short_desc:samsung*s5*
consists of two expressions, prod_short_desc:samsung*
and s5*
, which behave exactly the same as if you had written prod_short_desc:samsung* s5*
.
If you have compiled SQLite for the enhanced query syntax, you could use a query like:
prod_short_desc:samsung* prod_short_desc:s5* OR prod_name:panasonic* prod_name:tw*
If you have compiled SQLite for the standard query syntax, you cannot use a single query for this because the OR operator's precedence is too high and cannot be modified with parentheses.
So, I found the solution finally,
instead of searching from all the columns individually, I created a single column in the database which contains data for required columns to be searched into,
Example
I need to search in prod_name
& prod_short_desc
columns, so I created a column named data
in database and appended the values of prod_name
& prod_short_desc
then looking up into only data
field worked like a charm
prod_name | prod_short_desc
samsung | samsung s5
So, now I merged the data of both the columns into one with space as a seperator
data
samsung samsung s5
And then search was very fast indeed with below query,
select * from productsearch where productsearch match ('samsung*s5*')