MySql数据库索引优化

Question

dw_bill_detail_full_yw表已建索引字段precinctname、paidYear、paidMonth、paidDay，

EXPLAIN select precinctname,paidYear,paidMonth,paidDay,sum(chargepaid) chargepaid
        from dw_bill_detail_full_yw
        GROUP BY precinctname,paidYear,paidMonth,paidDay 
        order by precinctname,paidYear,paidMonth,paidDay;

但是提示 Using temporary; Using filesort，需要怎么优化索引？

Answer 1

删除单属性索引，建立组合索引，直接把这四个字段放到索引里去，顺序是precinctname,paidYear,paidMonth,paidDay

CREATE INDEX idx_dw_bill_detail_full_yw USING BTREE ON dw_bill_detail_full_yw (precinctname,paidYear,paidMonth,paidDay);