大家看看这个SQL语句怎么优化
如题，下面这个SQL语句，当数据很大时，就会卡死崩溃，请大家看看如何能优化。

SQL code

Select areaid, areaname, 'houseid' as hid, year, fzname,if(houproperties='0','公','私') as houproperties,projectid,project,sfyid,FORMAT(projectfee,2) as projectfee,    FORMAT(projectfee - Jan, 2) As Jan, FORMAT(projectfee - Feb, 2) As Feb, FORMAT(projectfee - Mar, 2) As Mar, FORMAT(projectfee - Apr, 2) As Apr,    FORMAT(projectfee - May, 2) As May, FORMAT(projectfee - Jun, 2) As Jun, FORMAT(projectfee - Jul, 2) As Jul, FORMAT(projectfee - Aug, 2) As Aug,    FORMAT(projectfee - Sep, 2) As Sep, FORMAT(projectfee - Oct, 2) As Oct, FORMAT(projectfee - Nov, 2) As Nov, FORMAT(projectfee - Dece, 2) As Dece,    FORMAT(projectfee*12 - (Case When sumyear is null Then 0 Else sumyear End), 2) As totalFrom (  Select tmpMain.*, areaname, fzname,houproperties,project,sfyid,         (Case tmpMain.projectid When '1001' Then rent When '1002' Then price*constarea Else price End) As projectfee /* Calculate the ShouldRent */  From (    Select h.areaid, h.houseid, h.year, projectid,      SUM(Case SUBSTR(sfqj, -2) When '01' Then jfje Else 0 End) As Jan,      SUM(Case SUBSTR(sfqj, -2) When '02' Then jfje Else 0 End) As Feb,      SUM(Case SUBSTR(sfqj, -2) When '03' Then jfje Else 0 End) As Mar,      SUM(Case SUBSTR(sfqj, -2) When '04' Then jfje Else 0 End) As Apr,      SUM(Case SUBSTR(sfqj, -2) When '05' Then jfje Else 0 End) As May,      SUM(Case SUBSTR(sfqj, -2) When '06' Then jfje Else 0 End) As Jun,      SUM(Case SUBSTR(sfqj, -2) When '07' Then jfje Else 0 End) As Jul,      SUM(Case SUBSTR(sfqj, -2) When '08' Then jfje Else 0 End) As Aug,      SUM(Case SUBSTR(sfqj, -2) When '09' Then jfje Else 0 End) As Sep,      SUM(Case SUBSTR(sfqj, -2) When '10' Then jfje Else 0 End) As Oct,      SUM(Case SUBSTR(sfqj, -2) When '11' Then jfje Else 0 End) As Nov,      SUM(Case SUBSTR(sfqj, -2) When '12' Then jfje Else 0 End) As Dece,      SUM(jfje) As sumyear /* All rent in the year */    From (       Select * From (           Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All  Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1  /* That's cross join, try to get all years */       ) hh, project p /* That's cross join, try to get all projects */       Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */          or (hh.houproperties = '1' and projectid ='1002'))    ) h       Left Outer Join charge c On c.areaid = h.areaid and c.hid = h.houseid and c.project = h.projectid and h.year = SUBSTR(sfqj, 1, 4)    Group By h.areaid, h.houseid, h.year, projectid  ) tmpMain /* Here we got the main result */    Left Outer Join house hh On tmpMain.areaid = hh.areaid and tmpMain.houseid = hh.houseid /* For calculate the ShouldRent */    Left Outer Join project pp On tmpMain.projectid = pp.projectid /* For calculate the ShouldRent, too */    Join area a On tmpMain.areaid = a.rowid /* For get the area's name */) tmpAll/* Here you can write some condition    */Order By areaid, houseid, year, projectid;

[解决办法]
贴出你的

explain select ...

以供分析。
[解决办法]
这么长的sql代码啊，怎么不考虑键视图或存储过程啊
[解决办法]
子查询太多，相互之间join之后，需要扫描的行上亿了。
[解决办法]
先执行内层，在以结果执行外层，不知道可以不

SQL code

 Select houseid,fzname,usearea,houproperties,sfyid,rent,areaid,year From house, ( Select distinct year From ( Select distinct SUBSTR(sfqj, 1, 4) as year From charge Union All  Select CAST(YEAR(sysdate()) As CHAR) as year ) tmp11 /* Get all years, and include current year */) tmp1  /* That's cross join, try to get all years */       ) hh, project p /* That's cross join, try to get all projects */       Where ((hh.houproperties = '0' and (projectid ='1001' or projectid = '1003')) /* Deal with the relationship between houproperties and projectid */          or (hh.houproperties = '1' and projectid ='1002'))