Multi-column search using full-text search (FTS) with multiple tokens using the OR operator

Question

I use FTS to query my database to increase the search speed, since I need to also search in the text description,

When I try to execute a query using a single column, it works fine, as shown below

select * from productsearch where productsearch match ('prod_name:panasonic*tw*') 

And

 select * from productsearch where productsearch match ('prod_short_desc:samsung*s5*') 

So, above both queries give the expected result, but when I try to combine both queries using the OR operator, it gives me no result

 select * from productsearch where productsearch match ('prod_name:panasonic*tw* OR prod_short_desc:samsung*s5*') 

So, I want to know if I am not doing something wrong with this when using the OR operator to search multiple columns

UPDATE

The below request works fine, but it doesn’t meet my requirement,

 select * from productsearch where productsearch match ('prod_name:panasonic* OR prod_short_desc:samsung*') 

You can see that if I delete several tokens, then its working tone is fine with the OR operator.

Answer 1

So, I found a solution, finally

instead of searching all the columns individually, I created one column in the database that contains the data for the required columns to search,

Example

I need to look in the prod_name and prod_short_desc , so I created a column called data in the database and added the values prod_name and prod_short_desc , and then looked only at the data field as a charm

prod_name | prod_short_desc

 samsung | samsung s5 

So now I have combined the data of both columns into one with a space as a separator

<strong> data

samsung samsung s5

And then the search was very fast using the below query,

 select * from productsearch where productsearch match ('samsung*s5*') 

Answer 2

SQLite FTS only supports simple search queries.

A request of type prod_short_desc:samsung*s5* consists of two expressions prod_short_desc:samsung* and s5* , which behave in exactly the same way as if you wrote prod_short_desc:samsung* s5* .

If you compiled SQLite for the extended query syntax, you can use a query such as:

 prod_short_desc:samsung* prod_short_desc:s5* OR prod_name:panasonic* prod_name:tw* 

If you compiled SQLite for the standard query syntax, you cannot use a single query for this because the priority of the OR operator is too high and cannot be changed using parentheses.