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
Subject
Views
Written By
Posted
Very slow when more than one full-text in query
5443
March 24, 2010 04:43AM
2512
March 24, 2010 09:55AM
2408
March 24, 2010 04:11PM
3874
March 24, 2010 06:30PM
2725
March 24, 2010 11:20PM
2558
March 25, 2010 04:50AM
2282
March 25, 2010 06:32AM
3707
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.