SELECT zy_detail_charge.ward_code,zy_bill_item.code,sum(zy_detail_charge.charge_fee)
FROM
zd_charge_item ,zy_detail_charge ,zy_bill_item,
(select distinct inpatient_no,admiss_times,ledger_sn from zy_receipt
where account_date >='2007-01-01' and account_date < '2007-02-01' ) a
WHERE ( zy_detail_charge.charge_code = zd_charge_item.code ) and
( zd_charge_item.bill_item_code = zy_bill_item.code ) and
( zy_detail_charge.inpatient_no = a.inpatient_no ) AND
( zy_detail_charge.admiss_times = a.admiss_times ) AND
( zy_detail_charge.ledger_sn = a.ledger_sn ) AND
zy_detail_charge.charge_status = '3' and
zy_detail_charge.ward_code = '3020100'
GROUP BY zy_detail_charge.ward_code,
zy_bill_item.code;
zy_detail_charge 这个表的数据超过100万条记录，花了十多分钟才能出结果，请问如何优化？每个表都有索引
------解决方案--------------------
- SQL code
--account_date时间列创聚集索引,zy_receipt表inpatient_no,admiss_times,ledger_sn建组合索引--zy_detail_charge表inpatient_no,admiss_times,ledger_sn建组合索引，charge_code另建一索引--zd_charge_item表Code建索引,zy_bill_item表建bill_item_code索引SELECT b.ward_code, c.code, sum(b.charge_fee) FROM zd_charge_item ajoin zy_detail_charge b on b.charge_code=a.Codejoin zy_bill_item c on a.bill_item_code=c.Codewhere exists(select 1 from zy_receipt where inpatient_no=b.inpatient_no and admiss_times=b.admiss_times and ledger_sn= b.ledger_sn and account_date between '2007-01-01 ' and '2007-01-31 23:59:59') and b.charge_status=3 and b.ward_code='3020100'group by b.ward_code,c.code
------解决方案--------------------
使用存在谓词exists的效率比连接的效率高很多，它使用的内存小。