"Suryya Ghosh" <[EMAIL PROTECTED]> wrote on 08/05/2005 09:50:27
AM:
> 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
One technique to speed up OR condition searches, tested again earlier this
week with reported success, is to split your OR-ed conditions into
UNION-ed queries like this:
(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)
UNION
(select field1, field2
from tblMerge
where
MATCH(field1)
AGAINST('water' IN BOOLEAN MODE)
ORDER BY field2 DESC LIMIT 500)
ORDER BY field2 DESC LIMIT 500;
This will not provide any kind of relevancy like your second test query
would but it will give you the top 500 records in descending field2 order
that contain any of the search words in field1. Since each part of the
UNION is a simple search, the FT index lookup will be faster so if you can
running just one query in about .5 seconds, the whole thing should execute
in about 3 seconds (to allow extra time for merging, deduplication, and
re-ordering of the final assembled sets of rows)
If you would like to favor records that have more search word hits
(relevancy) then you will need a different query, more like your second
test query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
