每天进步一点点——优化GROUP BY、or、和嵌套语句

优化GROUP BY语句

如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序：

mysql>explain select payment_date,sum(amount) from payment group by payment_date\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 16086

        Extra: Using temporary; Using filesort

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

mysql>explain select payment_date,sum(amount) from payment group by payment_dateorder by null\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ALL