日期:2014-05-16  浏览次数:20984 次

求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