MySQL慢速大查询
我有这个我想优化的大型查询，我已经优化了它，但它仍然很慢（> 1秒）：
select count(DISTINCT if(ps15.specification in ('All Season'),p.products_id,NULL)) as count1 ,count(DISTINCT if(ps15.specification in ('Winter'),p.products_id,NULL)) as count2 ,count(DISTINCT if(ps15.specification in ('Zomer'),p.products_id,NULL)) as count3 ,count(DISTINCT if(ps15.specification in ('Winter 2012'),p.products_id,NULL)) as count4 ,count(DISTINCT if(ps15.specification in ('Zomer 2013'),p.products_id,NULL)) as count5 ,count(DISTINCT if(ps15.specification in ('Winter 2013'),p.products_id,NULL)) as count6 ,count(DISTINCT if(ps15.specification in ('Zomer 2014'),p.products_id,NULL)) as count7
from (products p)
inner join (products_to_categories p2c)
on (p.products_id = p2c.products_id)
inner join (products_attributes pa)
on (p.products_id = pa.products_id)
inner join (products_options_values pv)
on (pa.options_values_id = pv.products_options_values_id)
inner join (products_stock ps)
on (p.products_id=ps.products_id
and pv.products_options_values_id = ps.products_options_values_id2
and ps.products_stock_quantity>0)
INNER JOIN products_specifications ps15
ON p.products_id = ps15.products_id
AND ps15.specifications_id = '15'
AND ps15.language_id = '1'
INNER JOIN products_specifications ps10
ON p.products_id = ps10.products_id
AND ps10.specifications_id = '10'
AND ps10.language_id = '1'
where p.products_status = '1'
and p2c.categories_id in (72,1,23,100,74,24,33,34,35,77,110,25,45,44,40,41,42,85,76,78,83,102,107,111,119,50,52,81,105,108,112,86,88,87,98,89,90,91,96,79,2,54,60,82,109,115,118,53,58,104,55,101,75,56,64,66,67,68,69,70,71,84,103,114,120,80,92,99,93,94,95,97,106,121)
AND ps10.specification in ('Meisje')
and products_options_values_name in ( 62,"3M/60cm","56-62","0-4 mnd","3m","0-3m","3-6m","3M","62/68","0-6m","50-62" , 68,"6M/67cm","9M/70cm","4-8 mnd","6m","3-6m","6M","62/68","0-6m" , 74,"4-8 mnd","8-12 mnd","6m","9m","6-9m","6M","9M","74/80","6-12m" );
输出是：
+--------+--------+--------+--------+--------+--------+--------+
| count1 | count2 | count3 | count4 | count5 | count6 | count7 |
+--------+--------+--------+--------+--------+--------+--------+
| 1 | 289 | 193 | 49 | 192 | 240 | 0 |
+--------+--------+--------+--------+--------+--------+--------+
解释mysql输出：
+----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+
| 1 | SIMPLE | p | index | PRIMARY,products_id | products_id | 5 | NULL | 4539 | Using where; Using index |
| 1 | SIMPLE | p2c | ref | PRIMARY | PRIMARY | 4 | kikleding.p.products_id | 1 | Using where; Using index |
| 1 | SIMPLE | ps15 | ref | products_id | products_id | 12 | kikleding.p2c.products_id,const,const | 1 | Using where |
| 1 | SIMPLE | ps10 | ref | products_id | products_id | 12 | kikleding.p.products_id,const,const | 1 | Using where |
| 1 | SIMPLE | pa | ref | idx_products_attributes_products_id | idx_products_attributes_products_id | 4 | kikleding.p.products_id | 6 | |
| 1 | SIMPLE | pv | ref | PRIMARY | PRIMARY | 4 | kikleding.pa.options_values_id | 2 | Using where |
| 1 | SIMPLE | ps | ref | idx_products_stock_attributes,products_id | idx_products_stock_attributes | 4 | kikleding.ps15.products_id | 6 | Using where |
+----+-------------+-------+-------+-------------------------------------------+-------------------------------------+---------+---------------------------------------+------+--------------------------+
我试图索引大部分表，ref在解释的第一行仍然给NULL。
它现在输出7列，有时我需要输出50列。
有什么建议？
ref = NULL仅意味着来自p表（即product ）的行不会与其他行进行连接。 这些行是您的查询中第一个被选中的行，并且来自其他表的行将被重新连接。 我总是期望EXPLAIN的第一行显示ref = NULL 。
基本上，你的执行计划说：
WHERE条件的products提取行 products.products_id字段中提取与来自（1）的行相匹配的行的products_to_categories行 建议的附加索引：
第一个应该明智地帮助，我不会对其他两个人有太多期望。
我认为问题在于你的许多COUNT(IF()) 。 这是骇人听闻的，引擎并没有真正针对这种查询进行优化。 相反，您必须瞄准返回像这样的结果集：
+---------------+-------+ | specification | count | +---------------+-------+ | All Season | 1 | +---------------+-------+ | Winter | 289 | +---------------+-------+ | ... | ... | +---------------+-------+
您的查询将如下所示：
SELECT
specification,
COUNT(*)
FROM products
JOIN ... -- your current JOIN list
GROUP BY specification -- this is the important bit
...应该几乎是即时的，即使没有额外的索引（或者可能在products_specifications(products_id, specification) ））