MySQL "OR MATCH" hangs (very slow) on multiple tables

Question

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

How is your table defined and which engine are you using? Could you show us your EXPLAIN SELECT...? — ablaeul
I put the EXPLAIN SELECT and the engine/collation, not sure how to show how it's defined (and I assume you're talking about the main table), unless you want the structure? And of which tables should I give? Thanks! — Kerry Jones

Kerry Jones Kerry Jones · Accepted Answer · 2010-05-24T09:03:42

I found 2 things slowing down my query drastically and fixed them.

To answer the first problem, it needed parentheses around the entire "MATCH AGAINST OR MATCH AGAINST":

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

I didn't understand how to use EXPLAIN SELECT, but it helped quite a bit, so thank you! This reduced that first number 16076 rows to 143. I then noticed the other two with over 23 and 25 thousand rows. That was cause from this line:

LEFT JOIN ( SELECT `product_id`, `image`, `swatch` FROM `product_images` WHERE `sequence` = 0) AS c 
    ON (a.`product_id` = c.`product_id`)

There was a reason I was doing this in the first place, which then changed. When I changed it, I didn't realize I could do a normal LEFT JOIN:

LEFT JOIN `product_images` AS c 
    ON (a.`product_id` = c.`product_id`)

This makes my final query like this: (and MUCH faster went from the 196 seconds to 0.0084 or so)

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 `product_images` 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 c.`sequence` = %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

Oh, and even before I was doing a full text search with multiple tables, it was taking about 1/2 a second. This is much improved.

MySQL "OR MATCH" hangs (very slow) on multiple tables

1 Answers