order by产生的 Using temporary的优化

今天遇到一个慢查询的sql,sql如下:

EXPLAIN
SELECT
	tas.f_year,
	tc.pk_id,
	tas.f_sex,
	tas.f_rank_score,
	tas.f_age,
	tas.f_km_five,
	tas.f_km_ten,
	tas.f_km_fifteen,
	tas.f_km_twenty,
	tas.f_km_halfway,
	tas.f_km_twentyfive,
	tas.f_km_thirty,
	tas.f_km_thirtyfive,
	tas.f_km_forty,
	tas.f_km_whole,
	tas.f_km_fifty,
	tas.f_km_hundred,
	tc.f_certifie_flag,
	tc.f_name AS tcf_name,
	tas.f_project,
	tas.f_finished_time,
	tas.f_good_score,
	tcs.f_begin_date
FROM
	mls.t_athlete_score AS tas
LEFT JOIN mls.t_comp_section AS tcs ON tas.fk_compsection_id = tcs.pk_id
LEFT JOIN mls.t_competition AS tc ON tas.fk_comp_id = tc.pk_id
WHERE
	tas.f_name = 'XXX'
AND upper(tas.f_card_num) = 'XXXX'
ORDER BY tcs.f_begin_date DESC

在这里插入图片描述
因为t_athlete_score 是主表,左连接到t_comp_section ,现在排序使用的第二个表的数据,所以产生了Using temporary,具体的产生原因,在
http://mysql.taobao.org/monthly/2015/03/04/
上讲的很清楚了,所以order by 产生Using temporary时,需要用主表的字段进行排序更合适一些,如果实在没有合适字段,再退而求其次。

EXPLAIN
SELECT
	tas.f_year,
	tc.pk_id,
	tas.f_sex,
	tas.f_rank_score,
	tas.f_age,
	tas.f_km_five,
	tas.f_km_ten,
	tas.f_km_fifteen,
	tas.f_km_twenty,
	tas.f_km_halfway,
	tas.f_km_twentyfive,
	tas.f_km_thirty,
	tas.f_km_thirtyfive,
	tas.f_km_forty,
	tas.f_km_whole,
	tas.f_km_fifty,
	tas.f_km_hundred,
	tc.f_certifie_flag,
	tc.f_name AS tcf_name,
	tas.f_project,
	tas.f_finished_time,
	tas.f_good_score,
	tcs.f_begin_date
FROM
	mls.t_athlete_score AS tas
LEFT JOIN mls.t_comp_section AS tcs ON tas.fk_compsection_id = tcs.pk_id
LEFT JOIN mls.t_competition AS tc ON tas.fk_comp_id = tc.pk_id
WHERE
	tas.f_name = 'XXX'
AND upper(tas.f_card_num) = 'XXXX'
#ORDER BY tcs.f_begin_date DESC
ORDER BY tas.f_date DESC;

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

MonkeyKing.sun

对你有帮助的话,可以打赏

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值