MySQL Forums
Forum List  »  Full-Text Search

slow performance due to OR condition on separate tables
Posted by: Jose-Miguel Pulido
Date: November 28, 2009 04:11PM

I have the following tables: products, product_categories, categories, product_tags and tags

Given a keyword that can either be a category name or a tag name (not known in advance), I want to find all products that either have a category with that name, or a tag with that name. I am using the query:

SELECT
p.product_id
FROM
products p, product_categories pc, categories c, product_tags pt, tags t
WHERE
(
p.product_id = pc.product_id
AND
pc.category_id = c.category_id
AND
MATCH(c.name) against ('keyword_here')
)
OR
(
p.product_id = pt.product_id
AND
pt.tag_id = t.tag_id
AND
MATCH(t.name) AGAINST ('keyword_here')
)


the query is slow. explain shows that it ends up visiting all rows in tables categories and tags. Indices are set correctly.

If I perform join decomposition and I break the query into two queries, one to search categories and another to search tags, performance improves significantly.

Anyone sees a way to achieve good performance in a single query?

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
slow performance due to OR condition on separate tables
3777
November 28, 2009 04:11PM


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.