MySQL Forums
Forum List  »  Full-Text Search

Very slow when more than one full-text in query
Posted by: James H
Date: March 24, 2010 04:43AM

This query takes 0.03 seconds:
RESET QUERY CACHE;
SELECT * FROM (fw_items fwi, fw_departments fwd, fw_aisles fwc) WHERE
(
MATCH(fwi.brand_name, fwi.product_name_and_size) AGAINST(+'BEER' IN BOOLEAN MODE)
)
AND fwi.department_id = fwd.id and fwi.category_id = fwc.id and fwc.department = fwd.id ;

This query takes 0.14 seconds:
RESET QUERY CACHE;
SELECT * FROM (fw_items fwi, fw_departments fwd, fw_aisles fwc) WHERE
(
MATCH(fwc.name) AGAINST(+'BEER' IN BOOLEAN MODE)
)
AND fwi.department_id = fwd.id and fwi.category_id = fwc.id and fwc.department = fwd.id ;

But this query takes 1.22 seconds:
RESET QUERY CACHE;
SELECT * FROM (fw_items fwi, fw_departments fwd, fw_aisles fwc) WHERE
(
MATCH(fwi.brand_name, fwi.product_name_and_size) AGAINST(+'BEER' IN BOOLEAN MODE)
or
MATCH(fwc.name) AGAINST(+'BEER' IN BOOLEAN MODE)
)
AND fwi.department_id = fwd.id and fwi.category_id = fwc.id and fwc.department = fwd.id ;
...but I'd have expected it to take abour 0.03 + 0.14 seconds.

Is this a problem with using multiple full-text searches in one query, or is it something more general? Any idea on how I can make the third query quicker?

Thanks,
James

Options: ReplyQuote


Subject
Views
Written By
Posted
Very slow when more than one full-text in query
5222
March 24, 2010 04:43AM
3772
March 24, 2010 06:30PM
2456
March 25, 2010 04:50AM
3586
March 26, 2010 07:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.