I am using FTS for querying my database for increasing the search speed as I need to search in text description also,
When I am trying to query using single column its working fine like below
select * from productsearch where productsearch match ('prod_name:panasonic*tw*')
And also,
select * from productsearch where productsearch match ('prod_short_desc:samsung*s5*')
So, above both queries give me expected result but when I try to combine both queries using OR
operator its not giving me any 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 doing anything wrong in here while using OR
operator for searching multiple columns
UPDATE
Below query is working fine but that doesn't fulfill my requirement,
select * from productsearch where productsearch match ('prod_name:panasonic* OR
prod_short_desc:samsung*')
You can see that if I remove the multiple tokens then its working fine with OR
operator too.
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*')