Re: slow performance due to OR condition on separate tables

[Rick James]

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 ]