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
5351
March 24, 2010 04:43AM
2485
March 24, 2010 09:55AM
2389
March 24, 2010 04:11PM
3836
March 24, 2010 06:30PM
2692
March 24, 2010 11:20PM
2524
March 25, 2010 04:50AM
2254
March 25, 2010 06:32AM
3677
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.