SQL查询Inner Join 与 Left Join速度影响

原始语句
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
 left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino ,
t_stockin  left join bs_comm as YL on si_ccid=YL.c_cid 
where di_csiwd=si_cwd and di_isino=si_ino and di_csiwd<>'' and di_csiwd is not null  and  di_ddate>='2008-09-09 00:00:00'
 and dcp_dstockin is null and  di_bvalid=0  order by di_ddate,di_cwd,di_ino
查询时间50s,记录1580
第一次修改,去除t_stockin,因为此表记录多，并且此查询中多余
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
 left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
  left join bs_comm as YL on di_ccid=YL.c_cid
where  di_csiwd<>'' and di_csiwd is not null  and  di_ddate>='2008-09-09 00:00:00'
 and dcp_dstockin is null and  di_bvalid=0  order by di_ddate,di_cwd,di_ino
查询时间20s
第二次修改,将left join 改成 inner join
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
 left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
  inner join bs_comm as YL on di_ccid=YL.c_cid
where  di_csiwd<>'' and di_csiwd is not null  and  di_ddate>='2008-09-09 00:00:00'
 and dcp_dstockin is null and  di_bvalid=0  order by di_ddate,di_cwd,di_ino
查询时间10s
第三次修改,将查询字段数目减少到1/3
select t_dealinfojg.* from t_dealinfojg
 left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
  inner join bs_comm as YL on di_ccid=YL.c_cid
where  di_csiwd<>'' and di_csiwd is not null  and  di_ddate>='2008-09-09 00:00:00'
 and dcp_dstockin is null and  di_bvalid=0  order by di_ddate,di_cwd,di_ino
查询时间为2s
第四次修改,将查询字段数目还原,去除排序
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
 left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
  inner join bs_comm as YL on di_ccid=YL.c_cid
where  di_csiwd<>'' and di_csiwd is not null  and  di_ddate>='2008-09-09 00:00:00'
 and dcp_dstockin is null and  di_bvalid=0
查询时间为7s
以下查询结果说明：inner join 与left join和查询字段数目多少对查询速度影响较大