Anything wrong with this MySQL quert? takes 10 seconds+ to load

Question

I have a search that is taking 10 seconds+ to execute! Keep in mind it is also searching over 200,000 products in the database. I posted the explain and MySQL query here.

1  SIMPLE  p  ref  PRIMARY,products_status,prod_prodid_status,product...  products_status  1  const  9048  Using where; Using temporary; Using filesort
1  SIMPLE  v  ref  PRIMARY,vendors_id,vendors_vendorid  vendors_vendorid  4  rhinomar_rhinomartnew.p.vendors_id  1   
1  SIMPLE  s  ref  products_id  products_id  4  rhinomar_rhinomartnew.p.products_id  1   
1  SIMPLE  pd  ref  PRIMARY,products,prod_desc_prodid_prodname  prod_desc_prodid_prodname  4  rhinomar_rhinomartnew.p.products_id  1   
1  SIMPLE  p2c  ref  PRIMARY,ptc_catidx  PRIMARY  4  rhinomar_rhinomartnew.p.products_id  1  Using where; Using index
1  SIMPLE  c  eq_ref  PRIMARY  PRIMARY  4  rhinomar_rhinomartnew.p2c.categories_id  1  Using where

MySQL Query:

Select p.products_id, p.products_image, p.products_price, p.products_weight,
       p.products_unit_quantity,
       s.specials_new_products_price, s.status,
       pd.products_name, pd.products_img_alt
From products p 
Left Join vendors v On v.vendors_id = p.vendors_id 
Left Join specials s On s.products_id = p.products_id 
Left Join products_description pd On pd.products_id = p.products_id 
Left Join products_to_categories p2c On p2c.products_id = p.products_id 
Left Join categories c On c.categories_id = p2c.categories_id 
Where
(  pd.products_name Like '%apparel%'
   Or p2c.categories_id In (773, 132, 135, 136, 119, 122, 124, 125, 126, 1749, 1753,
                            1747, 123, 127, 130, 131, 178, 137, 140, 164, 165, 166,
                            167, 168, 169, 832, 2045 )
   Or p.products_id = 'apparel'
   Or p.products_model = 'apparel'
   Or Concat( v.vendors_prefix, '-' ) = 'apparel'
   Or Concat( v.vendors_prefix, '-', p.products_id ) = 'apparel'
) 
And p.products_status = '1' 
And c.categories_status = '1' 
Group By p.products_id 
Order By pd.products_name

Answer 1

It seems you're only using products_description table in order to retrieve products_name and products_img_alt, Isn't it possible to have these two columns inside the products table?

Answer 2

Well, I would say the reason it takes so long to load is because it has to create a temporary table on the disk. (hence the Using temporary; Using filesort;)

I recently ran into this same problem when I used a "WHERE id IN (1,2,3,etc)" clause. Could you remove that just to test if it prevents the filesort?

Answer 3

As your EXPLAIN shows you, it's the products_status index that's being chosen, which I'm guessing is some kind of flag that shows whether the product is active? There's probably not a lot of granularity there, so you're likely not getting a lot of help from that.

I don't know whether MySQL optimizes this already, but you should order items in your first WHERE predicate - the (x OR y OR z) by likelihood of match and by descending expense of evaluation. Specifically, the p2c.category_id IN (...) should be at the beginning of the phrase, because that can use the index on categories. The CONCAT should be last in the phrase, and the LIKE should be in the middle.

Normally a UNION is the best way to split up an OR phrase, but because some elements of your OR list are expensive functions, that might not be the best choice for you.

You should consider whether you can normalize your data somewhat, and read Peter Lang's comment for some good observations.