执行这个SQL文,速度很慢,我想通过索引来提高一下速度,想请问一下,这个SQL中哪些字段都可以建立索引
select
rnk_purchase_info.shop_id as shop_id ,
rnk_purchase_info.item_id as item_id ,
sum(rnk_purchase_info.units) as quantity,
count( distinct rnk_purchase_info.order_num ) as order_num,
sum(rnk_purchase_info.price*rnk_purchase_info.units) as volume,
sum(rnk_purchase_info.price*rnk_purchase_info.units)/sum(rnk_purchase_info.units) as ppp,
count( distinct rnk_purchase_info.email ) as orderers ,
rnk_purchase_info.service_type as service_type
from rnk_purchase_info
inner join rnk_item_data
inner join rnk_shop_mst
inner join rnk_genreid_wk
left join rnk_shop_blacklist on rnk_purchase_info.shop_id=rnk_shop_blacklist.shop_id
and rnk_shop_blacklist.delflg=0
left join rnk_item_blacklist on rnk_purchase_info.shop_id=rnk_item_blacklist.shop_id
and rnk_purchase_info.item_id=rnk_item_blacklist.item_id and rnk_item_blacklist.delflg=0
where
rnk_purchase_info.shop_id =rnk_item_data.shop_id and rnk_purchase_info.item_id =rnk_item_data.item_id
and rnk_purchase_info.shop_id=rnk_shop_mst.shop_id and rnk_shop_mst.open_flg=1
and rnk_purchase_info.genre_id= rnk_genreid_wk.genre_id
and rnk_shop_blacklist.shop_id is null
and rnk_item_blacklist.item_id is null
and rnk_purchase_info.service_type=1
and rnk_purchase_info.mobile_flg=0
and rnk_purchase_info.prefecture=1
and rnk_item_data.inactive=0
and rnk_purchase_info.units > 0
and rnk_purchase_info.reg_date>=date_sub( '20061022 00:00:00',interval 1 day )
and rnk_purchase_info.reg_date < '20061028 00:00:00'
and rnk_purchase_info.gender_flg=0
and rnk_purchase_info.birth_year>=Year(now()) - 60
and rnk_purchase_info.birth_year<=Year(now()) - 15and rnk_purchase_info.price>=5
and rnk_purchase_info.price<=100and rnk_purchase_info.purchase_id <= 10000000group by shop_id,item_id,service_type
order by ppp desc,quantity desc
limit 200;
select
rnk_purchase_info.shop_id as shop_id ,
rnk_purchase_info.item_id as item_id ,
sum(rnk_purchase_info.units) as quantity,
count( distinct rnk_purchase_info.order_num ) as order_num,
sum(rnk_purchase_info.price*rnk_purchase_info.units) as volume,
sum(rnk_purchase_info.price*rnk_purchase_info.units)/sum(rnk_purchase_info.units) as ppp,
count( distinct rnk_purchase_info.email ) as orderers ,
rnk_purchase_info.service_type as service_type
from rnk_purchase_info
inner join rnk_item_data
inner join rnk_shop_mst
inner join rnk_genreid_wk
left join rnk_shop_blacklist on rnk_purchase_info.shop_id=rnk_shop_blacklist.shop_id
and rnk_shop_blacklist.delflg=0
left join rnk_item_blacklist on rnk_purchase_info.shop_id=rnk_item_blacklist.shop_id
and rnk_purchase_info.item_id=rnk_item_blacklist.item_id and rnk_item_blacklist.delflg=0
where
rnk_purchase_info.shop_id =rnk_item_data.shop_id and rnk_purchase_info.item_id =rnk_item_data.item_id
and rnk_purchase_info.shop_id=rnk_shop_mst.shop_id and rnk_shop_mst.open_flg=1
and rnk_purchase_info.genre_id= rnk_genreid_wk.genre_id
and rnk_shop_blacklist.shop_id is null
and rnk_item_blacklist.item_id is null
and rnk_purchase_info.service_type=1
and rnk_purchase_info.mobile_flg=0
and rnk_purchase_info.prefecture=1
and rnk_item_data.inactive=0
and rnk_purchase_info.units > 0
and rnk_purchase_info.reg_date>=date_sub( '20061022 00:00:00',interval 1 day )
and rnk_purchase_info.reg_date < '20061028 00:00:00'
and rnk_purchase_info.gender_flg=0
and rnk_purchase_info.birth_year>=Year(now()) - 60
and rnk_purchase_info.birth_year<=Year(now()) - 15and rnk_purchase_info.price>=5
and rnk_purchase_info.price<=100and rnk_purchase_info.purchase_id <= 10000000group by shop_id,item_id,service_type
order by ppp desc,quantity desc
limit 200;
解决方案 »
- mysql 时间问题 在线等
- MYSQL数据库异常
- 求教,mysql的event每天定时执行,不能执行成功的问题……
- mysql 更改存储过程再保存提示PROCEDURE _Navicat_Temp_Stored_Proc already exists
- mysql怎么样插入已删除ID字段?
- mysql中文的密码
- 100分求解:linux下mysql数据不能启动了的问题(Too many parameters )
- 求救~~!!请各位对mysql编程熟悉的哥哥帮帮我~~
- 求高手帮写一个sql
- 网站收到了这些乱七八糟留言,这是不是SQL注入?
- 做ASP.NET程序存取SQL数据库的时候,总是提示“SQL Server不存在或访问被拒绝”
- 请教一个mysql 中 yearweek 问题!!
执行过程当时花费的时间很长,所以想想法把SQL中的每一步进行
优化,首先认为索引一定会提高查询的速度.所以想求助一下高手
如何给哪个字段建立索引为好.