求mysql语句优化
小弟不才。。涉及两个表的操作。。于是写了如下冗长的mysql语句，求高手帮忙优化顺便指点下。
SELECT id,partner_id,
(SELECT TITLE FROM partner WHERE id=team.partner_id) as partner_title,
(SELECT location FROM partner WHERE id=team.partner_id) as partner_location,
(SELECT longlat FROM partner WHERE id=team.partner_id) as other,
(SELECT address FROM partner WHERE id=team.partner_id) as partner_address,
(SELECT homepage FROM partner WHERE id=team.partner_id) as homepage,
((team_price / market_price) *10) as ddd,sort_order,
title, userreview, product,now_number,group_id, FROM_UNIXTIME(expire_time) as expire_time, FROM_UNIXTIME( begin_time) AS begin_time, FROM_UNIXTIME(end_time) AS end_time,
team_price,summary ,market_price,image,summary,notice,detail FROM team
WHERE id=@id
ORDER by sort_order desc
——解决方案——————–
在
partner：ID
team：partner_id
上建立索引
——解决方案——————–
创建下面两个索引即可。
create index x1 on partner(id);
create index x2 on team(id,sort_order);
在team创建partner_id的索引，对这个索引毫无意义。
——解决方案——————–
创建索引后，语句可以改为如下。
- SQL code
select t.id,t.partner_id, p.TITLE, p,location, p,longlat , p,address , p,homepage, title, userreview, product,now_number,group_id, FROM_UNIXTIME(expire_time) as expire_time, FROM_UNIXTIME( begin_time) AS begin_time, FROM_UNIXTIME(end_time) AS end_time, team_price,summary ,market_price,image,summary,notice,detail from team t left join partner p on t.partner_id=p.id where t.id=@id ORDER by t.sort_order desc欢迎大家阅读《求MYSQL话语优化》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：求MYSQL话语优化
