帮忙优化一下sql,大概涉及到了六张表

*若价格不公道,可以让提问者在平台追加赏金哦,平台是您利益的保证

已完成
帮忙优化一下sql,大概涉及到了六张表-临
2年前发布
悬赏:30.0 元

问题详情:分类: MySQL

表最多的数据只有大概3W。但是连表查询的时候耗时2S,单表查询0.3S
select
tal.company_id as companyId,
tal.talent_id as talentId,
tal.talent_name as talentName,
tal.talent_phone as talentPhone,
tal.talent_age as talentAge,
tal.talent_birthday as talentBirthday,
tal.talent_sex as talentSex,
tal.`function` as functionIdStr,
tal.industry_type as industryTypeIdStr,
tal.province_id as provinceId,
pro.province_name as provinceName,
tal.city_id as cityId,
ci.city_name as cityName,
tal.is_marry as isMarry,
tal.work_experience as workExperience,
tal.channel_enterprise_id as channelEnterpriseId,
tal.political_status_id as politicalStatusId,
ad_poli.dict_name as politicalStatusName,
tal.talent_label as talentLabelIdStr,
tal.current_monthly_pay as currentMonthlyPay,
tal.expect_monthly_pay as expectMonthlyPay,
tal.talent_email as talentEmail,
tal.talent_place as talentPlace,
tal.expect_work_place as expectWorkPlace,
tal.expect_work_position as expectWorkPosition,
tal.talent_nationality as talentNationality,
tal.talent_nation as talentNation,
tal.talent_wechat as talentWechat,
tal.talent_qq as talentQQ,
tal.talent_idcard as talentIdcard,
tal.talent_height as talentHeight,
tal.is_multiple as isMultiple ,
tal.talent_telephone as talentTelephone,
tal.channel as channel,
tal.create_username as createUsername,
tal.modify_username as modifyUsername,
tal.create_time as createTime,
tal.modify_time as modifyTime
from talent tal
LEFT JOIN province pro
ON tal.province_id = pro.province_id
LEFT JOIN city ci
ON ci.city_id = tal.city_id
LEFT JOIN (select id,dict_name from admin_dictionaries) ad_poli
ON ad_poli.id = tal.political_status_id
LEFT JOIN (
select te.*,ad.dict_name from (
select talent_id,major_name,max(education_type) as education_type FROM talent_education
where is_hitherto = 1 GROUP BY talent_id
union
select talent_id,major_name,max(education_type) as education_type FROM talent_education
where (is_hitherto = 2 or is_hitherto is null) and talent_id not in (select talent_id from talent_education
where is_hitherto = 1 GROUP BY talent_id)
GROUP BY talent_id
) te
LEFT JOIN admin_dictionaries ad
ON te.education_type = ad.id
) maxSchool
ON maxSchool.talent_id = tal.talent_id
LEFT JOIN
(select talent_id, max(end_date) AS end_date ,work_company,work_position,`describe` FROM talent_work
where is_hitherto = 1 GROUP BY talent_id
union
select tws.talent_id, max(tws.end_date) AS end_date ,
tws.work_company,tws.work_position,tws.`describe`
FROM (select * from talent_work ORDER BY modify_time DESC) tws
where (is_hitherto = 2 or is_hitherto is null) and talent_id not in (select talent_id from talent_work
where is_hitherto = 1 GROUP BY talent_id)
GROUP BY talent_id
)maxWork

*若价格不公道,可以让提问者在平台追加赏金哦,平台是您利益的保证。你觉得当前的价格如何呢,奉上您珍贵的一票吧

虚高0人次 适中0人次 偏低0人次

分享海报会更快解决你的问题哦!分享海报

此处可发布评论

评论(4

176****21 能力:10

2019-10-31 16:14:18

这段sql没发全吧,最后的left join都没有on连接
阿姨洗铁路 能力:10

2019-10-31 15:43:15

再发一个别用in not in<br><br>not in肯定慢啊<br><br><br><br>in 不走索引的<br><br><br>这么多够了吧<br><br>
阿姨洗铁路 能力:10

2019-10-31 15:41:50

<br>其实用join就可以<br>好多in可以用join解决<br><br><br>非要用就用exists<br><br>
阿姨洗铁路 能力:10

2019-10-31 15:40:57

<br>让加索引啊<br><br><br>加并行参数<br><br><br>立马提升数倍<br><br>
点击加载更多
客服QQ 1913284695