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
5331
March 24, 2010 04:43AM
2479
March 24, 2010 09:55AM
2379
March 24, 2010 04:11PM
3828
March 24, 2010 06:30PM
2676
March 24, 2010 11:20PM
2514
March 25, 2010 04:50AM
2248
March 25, 2010 06:32AM
3666
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.