MySQL Bugs: #59557: MySQL uses order of fields in GROUP BY when ORDER BY NULL is specified

Bug #59557	MySQL uses order of fields in GROUP BY when ORDER BY NULL is specified
Submitted:	17 Jan 2011 15:26	Modified:	17 Jan 2011 16:03
Reporter:	Shlomo Priymak	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GROUP BY, order by, performance

Description:
Briefly: MySQL uses temporary table and copies data, when a simple index scan can be used, during a group by query.

When changing the order of fields in a group by clause to fit the relevant index, the database chooses the correct plan, since group by implies order by in MySQL. Alas, when using "order by null" to help the database disregard the order of the fields, no filesort is used, but a temporary table is *still used*. 

That is not needed, and caused severe performance degradation on several occasions, when the table is several GBs in size.

Might be related to this bug, if not exactly the same:
http://bugs.mysql.com/bug.php?id=56165

How to repeat:
With the following table:

CREATE TABLE `t` (
  `a` INT(11) NOT NULL,
  `b` INT(11) NOT NULL,
  PRIMARY KEY (`a`,`b`)
) ENGINE=INNODB;

Consider the following queries and explain plans:

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY a,b\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a ORDER BY NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using index; Using temporary
1 row in set (0.00 sec)

Thank you for the problem report. Indeed, optimizer could be smarter:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t` (
    ->   `a` INT(11) NOT NULL,
    ->   `b` INT(11) NOT NULL,
    ->   PRIMARY KEY (`a`,`b`)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values(1,1), (1,2), *1,3), (2,1), (2,2), (2,3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*1,3), (2,1), (2,2), (2,3)' at line 1
mysql> insert into t values(1,1), (1,2), (1,3), (2,1), (2,2), (2,3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY a,b\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6
        Extra: Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6
        Extra: Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t GROUP BY b,a ORDER BY NULL\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 6
        Extra: Using index; Using temporary
1 row in set (0.00 sec)