mysql select count慢,为什么MySQL select count（*）在快速子查询上这么慢？

I have the following query that executes quite fast:

SELECT DISTINCT p.products_id,

p.products_image,

p.products_quantity,

p.products_status,

m.manufacturers_id,

p.products_date_added,

p.products_subimage1,

pd.products_name,

p.products_price,

p.products_length,

p.products_width,

p.products_height,

p.products_tax_class_id,

IF(s.status, s.specials_new_products_price, NULL) AS

specials_new_products_price,

IF(s.status, s.specials_new_products_price, p.products_price) AS

final_price,

IF(clearance_price < products_cost * 2.25,

clearance_price,

products_cost * 2.25) AS

sorting_price

FROM

(SELECT products_id ,

IF(clearance_price < products_cost * 2.25,

clearance_price,

products_cost * 2.25) AS

sorting_price

FROM `products`

ORDER BY products_id DESC)q,

products p

left join manufacturers m USING(manufacturers_id)

left join specials s

ON p.products_id = s.products_id

left join products_attributes pa

ON p.products_id = pa.products_id

left join products_options po

ON pa.options_id = po.products_options_id

left join products_options_values pov

ON pa.options_values_id = pov.products_options_values_id,

products_description pd,

categories c,

products_to_categories p2c

WHERE

q.products_id = p.products_id

AND q.sorting_price = sorting_price

AND

p.products_status = '1'

AND p.products_id = pd.products_id

AND pd.language_id = '1'

AND p.products_id = p2c.products_id

AND p2c.categories_id = c.categories_id

AND (( pd.products_name LIKE '%a%'

OR po.products_options_name LIKE '%a%'

OR pov.products_options_values_name LIKE '%a%'

OR pd.products_description LIKE '%a%' ))

When I wrap it inside a count(*) query, the new count query takes 10 to 15 times more time, very slow.

I wrap it like so:

SELECT count(*) as total from (

SELECT DISTINCT p.products_id,

p.products_image,

p.products_quantity,

p.products_status,

m.manufacturers_id,

p.products_date_added,

p.products_subimage1,

pd.products_name,

p.products_price,

p.products_length,

p.products_width,

p.products_height,

p.products_tax_class_id,

IF(s.status, s.specials_new_products_price, NULL) AS

specials_new_products_price,

IF(s.status, s.specials_new_products_price, p.products_price) AS

final_price,

IF(clearance_price < products_cost * 2.25,

clearance_price,

products_cost * 2.25) AS

sorting_price

FROM

(SELECT products_id ,

IF(clearance_price < products_cost * 2.25,

clearance_price,

products_cost * 2.25) AS

sorting_price

FROM `products`

ORDER BY products_id DESC)q,

products p

left join manufacturers m USING(manufacturers_id)

left join specials s

ON p.products_id = s.products_id

left join products_attributes pa

ON p.products_id = pa.products_id

left join products_options po

ON pa.options_id = po.products_options_id

left join products_options_values pov

ON pa.options_values_id = pov.products_options_values_id,

products_description pd,

categories c,

products_to_categories p2c

WHERE

q.products_id = p.products_id

AND q.sorting_price = sorting_price

AND

p.products_status = '1'

AND p.products_id = pd.products_id

AND pd.language_id = '1'

AND p.products_id = p2c.products_id

AND p2c.categories_id = c.categories_id

AND (( pd.products_name LIKE '%a%'

OR po.products_options_name LIKE '%a%'

OR pov.products_options_values_name LIKE '%a%'

OR pd.products_description LIKE '%a%' ))

) AS derivedtable1

Why does this happen? Is there any way to optimize this?

EDIT:

This is the EXPLAIN EXTENDED of the first query:

This is the EXPLAIN EXTENDED of the count (the second) query:

This is the PROFILING of the first query:

This is the PROFILING of the count (the second) query:

解决方案FROM pd

LEFT JOIN po ON ...

WHERE ( ... OR po.x LIKE '...' OR ... )

The semantics of LEFT say that it does not matter whether there is a matching row in po. Hence the LIKE has zero impact on the outcome of the query. Suggest you get rid of OR ... LIKE of any items in LEFT. Or maybe you did not really mean LEFT? That could make it run faster, but the result set could be different.

I see from the EXPLAINs that there is no good excuse for the difference in timing. Oh, how are you running the queries? The first one has 213K rows of output -- are you waiting for all of it to come back before stopping your stopwatch?