执行这个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;
解决方案 »
- wordpress 中的sql语句orderby 问题
- 挑战性优化问题! 此贴20分 转连接贴! 如有解决问题可另帖加分!
- 问个存储方式的问题
- !!!如何把mysql表从一个数据库移植到另一个数据库 !! 在线等!!
- 这条SQL该如何写?
- 如何在vc中配置mysql++环境?多谢!!
- 请问MySql什么时候会把索引放入 key_buffer
- 怎样得到这样的数据
- 求MYSQL筛选同一个IP登录不同帐号的情况
- 我用管理员身份运行mysql,显示拒绝访问是什么原因
- 做ASP.NET程序存取SQL数据库的时候,总是提示“SQL Server不存在或访问被拒绝”
- 请教一个mysql 中 yearweek 问题!!
执行过程当时花费的时间很长,所以想想法把SQL中的每一步进行
优化,首先认为索引一定会提高查询的速度.所以想求助一下高手
如何给哪个字段建立索引为好.