Hi,
We have a table containing more than 15 million rows of data, can anybody
please help in this problem of fulltext search described below.
The following query is giving a good result in terms of query time.
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
when I want to search for more that one word or phrase like food, locomotive,
water tank then the query is not working up to the desired level. The following
query is taking not less than 90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
I have also tried with another version of the same previous query which is also
not giving a desired performance. The following query is taking not less than
90 to 100 seconds that is 1.5 minutes
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food locomotive water tank' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
In these cases, I think, I am not getting the advantage of full text search,
because still it is taking a lot of time to execute the query. Can I optimise
the database to few step forward such that the query time can be reduced for
multiple words of phrases, please let us know can the query be optimized to
provide a better performance?
Regards,
Suryya
