Mysql sql语句优化

[c136900141]

select sum( PNPDQ1 ) PNPDQ, tstms1
from
(
SELECT sum( PNPDQ ) PNPDQ1,(
case
when tstms between '2010/12/15 8:30:00' and '2010/12/15 9:30:00' then '08:30-09:30'
when tstms between '2010/12/15 9:30:00' and '2010/12/15 10:30:00' then '09:30-10:30'
when tstms between '2010/12/15 10:30:00' and '2010/12/15 11:30:00' then '10:30-11:30'
when tstms between '2010/12/15 11:30:00' and '2010/12/15 12:30:00' then '11:30-12:30'
when tstms between '2010/12/15 12:30:00' and '2010/12/15 13:30:00' then '12:30-13:30'
when tstms between '2010/12/15 13:30:00' and '2010/12/15 14:30:00' then '13:30-14:30'
when tstms between '2010/12/15 14:30:00' and '2010/12/15 15:30:00' then '14:30-15:30'
when tstms between '2010/12/15 15:30:00' and '2010/12/15 16:30:00' then '15:30-16:30'
when tstms between '2010/12/15 16:30:00' and '2010/12/15 17:30:00' then '16:30-17:30'
when tstms between '2010/12/15 17:30:00' and '2010/12/15 18:30:00' then '17:30-18:30'
when tstms between '2010/12/15 18:30:00' and '2010/12/15 19:30:00' then '18:30-19:30'
else '19:30-20:30'end) tstms1
FROM
AUTOM.AUTRN00F B inner join AUTOM.AUPNO00F A on A.PNCOD=B.TSPNO
where tslin='SMT01' and tscau IN ('CH0001','CH0002','TR00IL') and tstms between '2010/12/15 8:30:00' and '2010/12/15 20:30:00' group by tstms) aa group by tstms1
哪位高手那否帮我优化这个Sql语句
谢谢了！

[wwwwb]

EXPLAI SQL语句，贴结果
在tstms、PNCOD、TSPNO 上建立复合索引试试

[ACMAIN_CHM]

贴出你的下面四个结果。

show index from AUTRN00F;
show index from AUPNO00F;
explain SELECT sum( PNPDQ ) PNPDQ1,(
case
when tstms between '2010/12/15 8:30:00' and '2010/12/15 9:30:00' then '08:30-09:30'
when tstms between '2010/12/15 9:30:00' and '2010/12/15 10:30:00' then '09:30-10:30'
when tstms between '2010/12/15 10:30:00' and '2010/12/15 11:30:00' then '10:30-11:30'
when tstms between '2010/12/15 11:30:00' and '2010/12/15 12:30:00' then '11:30-12:30'
when tstms between '2010/12/15 12:30:00' and '2010/12/15 13:30:00' then '12:30-13:30'
when tstms between '2010/12/15 13:30:00' and '2010/12/15 14:30:00' then '13:30-14:30'
when tstms between '2010/12/15 14:30:00' and '2010/12/15 15:30:00' then '14:30-15:30'
when tstms between '2010/12/15 15:30:00' and '2010/12/15 16:30:00' then '15:30-16:30'
when tstms between '2010/12/15 16:30:00' and '2010/12/15 17:30:00' then '16:30-17:30'
when tstms between '2010/12/15 17:30:00' and '2010/12/15 18:30:00' then '17:30-18:30'
when tstms between '2010/12/15 18:30:00' and '2010/12/15 19:30:00' then '18:30-19:30'
else '19:30-20:30'end) tstms1
FROM
AUTOM.AUTRN00F B inner join AUTOM.AUPNO00F A on A.PNCOD=B.TSPNO
where tslin='SMT01'
and tscau IN ('CH0001','CH0002','TR00IL')
and tstms between '2010/12/15 8:30:00' and '2010/12/15 20:30:00'
group by tstms;
explain select sum( PNPDQ1 ) PNPDQ, tstms1
from
(
SELECT sum( PNPDQ ) PNPDQ1,(
case
when tstms between '2010/12/15 8:30:00' and '2010/12/15 9:30:00' then '08:30-09:30'
when tstms between '2010/12/15 9:30:00' and '2010/12/15 10:30:00' then '09:30-10:30'
when tstms between '2010/12/15 10:30:00' and '2010/12/15 11:30:00' then '10:30-11:30'
when tstms between '2010/12/15 11:30:00' and '2010/12/15 12:30:00' then '11:30-12:30'
when tstms between '2010/12/15 12:30:00' and '2010/12/15 13:30:00' then '12:30-13:30'
when tstms between '2010/12/15 13:30:00' and '2010/12/15 14:30:00' then '13:30-14:30'
when tstms between '2010/12/15 14:30:00' and '2010/12/15 15:30:00' then '14:30-15:30'
when tstms between '2010/12/15 15:30:00' and '2010/12/15 16:30:00' then '15:30-16:30'
when tstms between '2010/12/15 16:30:00' and '2010/12/15 17:30:00' then '16:30-17:30'
when tstms between '2010/12/15 17:30:00' and '2010/12/15 18:30:00' then '17:30-18:30'
when tstms between '2010/12/15 18:30:00' and '2010/12/15 19:30:00' then '18:30-19:30'
else '19:30-20:30'end) tstms1
FROM
AUTOM.AUTRN00F B inner join AUTOM.AUPNO00F A on A.PNCOD=B.TSPNO
where tslin='SMT01'
and tscau IN ('CH0001','CH0002','TR00IL')
and tstms between '2010/12/15 8:30:00' and '2010/12/15 20:30:00'
group by tstms
) aa group by tstms1;