MySQL Forums
Forum List  »  Full-Text Search

Re: slow performance due to OR condition on separate tables
Posted by: Rick James
Date: November 29, 2009 04:14PM

Currently
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') )

See if this works better:

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

If you then need to fetch stuff from `product`, do something like
SELECT *
    FROM product p,
         ( the union above ) u
    WHERE p.product_id = u.product_id

To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: slow performance due to OR condition on separate tables
2461
November 29, 2009 04:14PM


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.