求助，帮忙做下这个sql语句的优化

现在这个sql查询要7秒！
太慢了，求优化....

SELECT 
(SELECT c1.name from weather_phenomenons c1 where c1.code=a.one_code) one_name,
(SELECT c2.name from weather_phenomenons c2 where c2.code=a.two_code) two_name,
(SELECT c1.image from weather_phenomenons c1 where c1.code=a.one_code) image,
(SELECT c2.image from weather_phenomenons c2 where c2.code=a.two_code) image1,
(SELECT t.info_title from infos t where t.info_type=106 and info_status=3 order by info_time desc limit 1)title,
a.low_temperature,a.high_temperature,a.weather_day,
c.sname,c.t,c.pr1,c.wd,c.ws,c.wet 
FROM weathers a,cities b,sc_realtime_weathers c 
where a.area_key=b.city_key and c.sid=b.code AND a.weather_day between'2012-02-16' and '2012-02-19' and c.st='2012-02-16 18:00:00'and (c.sname like'绵阳%' or b.code='绵阳' or b.phoneticize='绵阳')

(SELECT c1.name from weather_phenomenons c1 where c1.code=a.one_code) one_name,  
(SELECT c2.name from weather_phenomenons c2 where c2.code=a.two_code) two_name,  
(SELECT c1.image from weather_phenomenons c1 where c1.code=a.one_code) image,  
(SELECT c2.image from weather_phenomenons c2 where c2.code=a.two_code) image1,
这几个是不是可以合并为两个SQL

看看执行计划
先做过滤 然后做表连接

另 表上对应字段做index么 或者是联合index

select c1.name one_name, c2.name two_name, c3.image image, c4.image image1,
	   (SELECT t.info_title from infos t where t.info_type=106 and info_status=3 order by info_time desc limit 1) title,
	   a.low_temperature,a.high_temperature,a.weather_day,  
	   c.sname,c.t,c.pr1,c.wd,c.ws,c.wet 
	   FROM weathers a,cities b,sc_realtime_weathers c ,weather_phenomenons c1,weather_phenomenons c2,weather_phenomenons c3,weather_phenomenons c4
	   where a.area_key=b.city_key and c.sid=b.code AND a.weather_day between'2012-02-16' and '2012-02-19' and c.st='2012-02-16 18:00:00'and (c.sname like'绵阳%' or b.code='绵阳' or b.phoneticize='绵阳')
	   and c1.code=a.one_code and c2.code=a.two_code and c3.code=a.one_code and c4.code=a.two_code

 

 

还是得看执行计划才能从哪入手啊，   info_type 和info_status 是常量还是变量，是变量的话 cost 可能在这了

1、上执行计划

2、拆SQL看看效率
SELECT
a.one_code,
a.two_code,
a.low_temperature,a.high_temperature,a.weather_day,
c.sname,c.t,c.pr1,c.wd,c.ws,c.wet
FROM weathers a,cities b,sc_realtime_weathers c
where a.area_key=b.city_key and c.sid=b.code AND a.weather_day between'2012-02-16' and '2012-02-19' and c.st='2012-02-16 18:00:00'and (c.sname like'绵阳%' or b.code='绵阳' or b.phoneticize='绵阳')

//这个好像查询后可以复用 没必要放到里边吧
(SELECT t.info_title from infos t where t.info_type=106 and info_status=3 order by info_time desc limit 1)title

select
  (SELECT c1.name from weather_phenomenons c1 where c1.code=?) one_name, 
  (SELECT c2.name from weather_phenomenons c2 where c2.code=?) two_name, 
  (SELECT c1.image from weather_phenomenons c1 where c1.code=?) image, 
  (SELECT c2.image from weather_phenomenons c2 where c2.code=?) image1
from dual

关键还是上执行计划   另外表之间连接太多也不好  还不如拆成几条SQL效率高