MySQL "OR MATCH" hangs (very slow) on multiple tables
- by Kerry
After learning how to do MySQL Full-Text search, the recommended solution for multiple tables was OR MATCH and then do the other database call. You can see that in my query below.
When I do this, it just gets stuck in a "busy" state, and I can't access the MySQL database.
SELECT
a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, b.`price`, c.`image`, c.`swatch`, e.`name` AS industry,
MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) AS relevance
FROM
`products` AS a LEFT JOIN `website_products` AS b
ON (a.`product_id` = b.`product_id`)
LEFT JOIN ( SELECT `product_id`, `image`, `swatch` FROM `product_images` WHERE `sequence` = 0) AS c
ON (a.`product_id` = c.`product_id`)
LEFT JOIN `brands` AS d
ON (a.`brand_id` = d.`brand_id`)
INNER JOIN `industries` AS e ON (a.`industry_id` = e.`industry_id`)
WHERE
b.`website_id` = %d
AND b.`status` = %d
AND b.`active` = %d
AND MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE )
OR MATCH ( d.`name` ) AGAINST ( '%s' IN BOOLEAN MODE )
GROUP BY a.`product_id`
ORDER BY relevance DESC
LIMIT 0, 9
Any help would be greatly appreciated.
EDIT
All the tables involved are MyISAM, utf8_general_ci.
Here's the EXPLAIN SELECT statement:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 16076 Using temporary; Using filesort
1 PRIMARY b ref product_id product_id 4 database.a.product_id 2
1 PRIMARY e eq_ref PRIMARY PRIMARY 4 database.a.industry_id 1
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 23261
1 PRIMARY d eq_ref PRIMARY PRIMARY 4 database.a.brand_id 1 Using where
2 DERIVED product_images ALL NULL NULL NULL NULL 25933 Using where
I don't know how to make that look neater -- sorry about that
UPDATE
it returns the query after 196 seconds (I think correctly). The query without multiple tables takes about .56 seconds (which I know is really slow, we plan on changing to solr or sphinx soon), but 196 seconds??
If we could add a number to the relevance if it was in the brand name ( d.name ), that would also work
