MYSQL GroupBY -JOIN的优化

[tianlianchao1982]

一个表groupby 后还有两万多条 和另一个表 （3000条）join ，怎么能让它速度好点呢 现在是4秒多才能查出来

相关语句如下:




#set @shop_id = 45;
#set @product_code = '22230044';
#set @season_id = 586 ;
#set @erp_category_id = 264;
#set @tag_info = '热卖';
set @start_on_sale_date = '1900-1-1';
set @end_on_sale_date = '2050-1-1';
set @start_day = '2012-6-21';
set @end_day = '2012-6-27';
set @min_sale_number =-2147000000;
set @max_sale_number =2147000000;
set @min_can_use_stock =-2147000000;
set @max_can_use_stock =2147000000;
set @warehouse_id = 1;

EXPLAIN
SELECT count(1)
/*tb_ppi.product_code as 款号
,tb_ppi.product_name as 商品名称
#,tb_ppi.image as 图片
,tb_ppi.season_name as 季节
,tb_ppi.erp_category_name as 品类
,tb_pti.product_tag_manual as 标签
,tb_posi.on_sale_date as 上架日期
,tb_apsdc.shop_name as 店铺名称
,tb_apsdc.cost_price as 成本价
,tb_apsdc.network_price as 网络价
,tb_apsdc.sale_money as 销售金额
,tb_apsdc.sale_price as 实际成交均价
,tb_apsdc.sale_number as 实际成交件数
,tb_apsdc.sale_number / (DATEDIFF(@end_day,@start_day)+1) as 日均销售件数
,tb_apsdc.sale_price / tb_apsdc.cost_price as 实际倍率
,tb_apsdc.sale_price / tb_apsdc.network_price as 平均折扣率
,tb_lzisr.ipv as 浏览量
,tb_lzisr.iuv as 访客数
,tb_lzisr.alipay_winner_num as 成交顾客数
,tb_lzisr.roc as 转化率
,tb_spsi.warehouse_name as 仓库名称
,tb_spsi.quality_stock as 库存数量
,tb_spsi.can_use_stock as 可用减
,tb_sps.not_in_wh_number as 在途量
,(tb_spsi.can_use_stock + tb_sps.not_in_wh_number ) as 可用量
,(((tb_spsi.can_use_stock) /(tb_apsdc.sale_number / (DATEDIFF(@end_day,@start_day)+1) * 0.8))+3) as 库存消化天数
,(((tb_spsi.can_use_stock + tb_sps.not_in_wh_number ) /(tb_apsdc.sale_number / (DATEDIFF(@end_day,@start_day)+1) * 0.8))+3) as 可用量消化天数
,date_add(curdate(),INTERVAL ((tb_spsi.can_use_stock /(tb_apsdc.sale_number / (DATEDIFF(@end_day,@start_day)+1) * 0.8))+3) day ) as 消化完成日期
*/
FROM `product_product_info` as tb_ppi
join (
select product_id,shop_id
,shop_name

,avg(cost_price) as cost_price
,avg(network_price) as network_price
,avg(sale_price) as sale_price
,sum(sale_number) as sale_number
,sum(sale_money) as sale_money
from `analysis_product_sale_day_count`
where ( (analysis_date between @start_day and @end_day)
and ( shop_id in(34,35,45,52))# = 34 or shop_id = 35 or shop_id = 45 or shop_id = 52)
#and shop_id = @shop_id
#and product_code=@product_code
)
group by product_id,shop_id
,shop_name

#having (
# sum(sale_number) between @min_sale_number and @max_sale_number
# )
) as tb_apsdc
on (
tb_ppi.product_id=tb_apsdc.product_id
#and tb_ppi.product_code = @product_code
#and tb_ppi.season_id = @season_id
#and tb_ppi.erp_category_id = @erp_category_id
#and tb_apsdc.shop_id = @shop_id
)

请问如何优化?

[ACMAIN_CHM]

explain select ...
show index from ,,,,

贴出来以供分析。

[tianlianchao1982]

show indexes from `product_product_info`;

product_product_info 0 PRIMARY 1 product_id A 5721 BTREE
product_product_info 1 product_code 1 product_code A 5721 BTREE


show indexes from `analysis_product_sale_day_count`;
analysis_product_sale_day_count 0 PRIMARY 1 sku_id A 17688 BTREE
analysis_product_sale_day_count 0 PRIMARY 2 shop_id A 53066 BTREE
analysis_product_sale_day_count 0 PRIMARY 3 analysis_date A 212266 BTREE
analysis_product_sale_day_count 1 idx_analysis_date 1 analysis_date A 96 BTREE
analysis_product_sale_day_count 1 idx_shop_id 1 shop_id A 6 BTREE
analysis_product_sale_day_count 1 idx_product_code 1 product_code A 4005 YES BTREE
analysis_product_sale_day_count 1 idx_sku_id 1 sku_id A 26533 BTREE
analysis_product_sale_day_count 1 xx 1 shop_id A 14 BTREE
analysis_product_sale_day_count 1 xx 2 shop_name A 14 YES BTREE
analysis_product_sale_day_count 1 xx 3 product_code A 30323 YES BTREE
analysis_product_sale_day_count 1 idx_product_sale 1 product_id A 5896 YES BTREE
analysis_product_sale_day_count 1 idx_product_sale 2 shop_id A 17688 BTREE
analysis_product_sale_day_count 1 idx_product_sale 3 shop_name A 17688 YES BTREE
analysis_product_sale_day_count 1 idx_product_id 1 product_id A 2122 YES BTREE


EXPLAIN:


1 PRIMARY <derived2> ALL 1458
1 PRIMARY tb_ppi eq_ref PRIMARY PRIMARY 4 tb_apsdc.product_id 1 Using index
2 DERIVED analysis_product_sale_day_count range idx_analysis_date,idx_shop_id,xx idx_analysis_date 3 30042 Using where; Using temporary; Using filesort

[Rotel-刘志东]

explain select....执行计划是怎样的。
show index from tb...