小弟不才。。涉及两个表的操作。。于是写了如下冗长的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 / et_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 ,et_price,image,summary,notice,detail FROM team
WHERE id=@id
ORDER by sort_order desc
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 / et_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 ,et_price,image,summary,notice,detail FROM team
WHERE id=@id
ORDER by sort_order desc
解决方案 »
- mysql 获得表记录总数的效率问题 急急急
- 求解:MYSQL tirgger触发器 delete 2个表中的是数据 请大师帮忙
- MySQL的正则中,“\”转义的问题
- net start mysql 发生系统错误 2。系统找不到指定的文件
- "ACMAIN_CHM"给看看driver has not received any packets from the server
- mysql编码转换
- 关于load data local 问题?
- Mysql5.0, 我用Mysql administrator里edit table设置列的charset为gb2312可以生效,为啥设置table option为gb2312却不生效?
- 简单问题
- MYSQL查询获取当前时间,一条记录减少一秒如何操作
- 计划任务调用存储过程,失败了,求助
- 都是一个commit里的,这两种写法哪个更好
partner:ID
team:partner_id
上建立索引
create index x1 on partner(id);
create index x2 on team(id,sort_order);
在team创建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 ,et_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