Hello.
The common solution for similar issues which appeared on the
list not recently was modifying the query to use UNIONs. For
example:
select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
Or
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
Order By field2 DESC LIMIT 500
can be changed to:
(SELECT field1, field2
FROM tblMerge
WHERE
MATCH(field1)
AGAINST('food' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
UNION
(SELECT field1, field2
FROM tblMerge
WHERE
MATCH(field1)
AGAINST('locomotive' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
ORDER BY field2 DESC LIMIT 500
Check if the query with UNION faster than with OR.
Suryya Ghosh" <[EMAIL PROTECTED]> wrote:
>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
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
