MySQL Forums
Forum List  »  Full-Text Search

Using UNION instead of OR helps...
Posted by: James H
Date: March 24, 2010 06:30PM

I've taken out the OR and inserted a UNION (with some INNER JOINS that make no difference to performance). The query now completes in about 0.35secs...

RESET QUERY CACHE;
(
SELECT * FROM fw_items fwi
INNER JOIN fw_departments fwd ON fwd.id = fwi.department_id
INNER JOIN fw_aisles fwc ON fwc.id = fwi.category_id
WHERE MATCH(fwi.brand_name, fwi.product_name_and_size) AGAINST('BEER' IN BOOLEAN MODE)
)
union
(
SELECT * FROM fw_items fwi
INNER JOIN fw_departments fwd ON fwd.id = fwi.department_id
INNER JOIN fw_aisles fwc ON fwc.id = fwi.category_id
WHERE MATCH(fwc.name) AGAINST('BEER' IN BOOLEAN MODE)
)

...but performing these two SELECT statements individually takes 0.03secs + 0.12secs = 0.15secs. So how come it takes more than double this amount of time (i.e., 0.35secs) when they're together in a UNION like this? (The resultset is not that big - approx. 700 rows.)

Cheers,
James

Options: ReplyQuote


Subject
Views
Written By
Posted
Using UNION instead of OR helps...
3793
March 24, 2010 06:30PM
2482
March 25, 2010 04:50AM
3629
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.