MySQL Bugs: #60315: Slow ORDER BY function() if using GROUP BY

Bug #60315	Slow ORDER BY function() if using GROUP BY
Submitted:	3 Mar 2011 14:03	Modified:	15 Jan 2013 14:40
Reporter:	Richard Teubel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.1.51, 5.5.8	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, GROUP BY, order by, slow

Description:
I have a Table with many rows and i would like to group the result with GROUP BY. If the result is ordered by a column, everything is ok and fast. If the result is ordered by a alias from a function, the query need very long time.

SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias
FROM myTable
GROUP BY myGroup;
+----+---------+-------------------+-------------------+
| ID | myGroup | myContent         | slowFunctionAlias |
+----+---------+-------------------+-------------------+
|  6 |       1 | oV#Nj-,[UkF9\i[8& |              NULL |
|  1 |       2 | +=Z\X:B|d:mK #5mc |              NULL |
+----+---------+-------------------+-------------------+
2 rows in set (2.00 sec)  <= OK and fast!

SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias
FROM myTable
GROUP BY myGroup
ORDER BY slowFunctionAlias; 
+----+---------+-------------------+-------------------+
| ID | myGroup | myContent         | slowFunctionAlias |
+----+---------+-------------------+-------------------+
|  6 |       1 | oV#Nj-,[UkF9\i[8& |                 1 |
|  1 |       2 | +=Z\X:B|d:mK #5mc |                 1 |
+----+---------+-------------------+-------------------+
2 rows in set (20.02 sec) <= the same result, but very long time!

In the secound query the execution count of function slowFunction() is 20, for each row in table. In the first query only for every row of result (2)!

How to repeat:
=>> Please show sloworderby.sql to repeat. <<=

mysql> CREATE TABLE IF NOT EXISTS `myTable` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `myGroup` int(11) DEFAULT NULL,
    ->   `myContent` varchar(20) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `Group` (`myGroup`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

INSERT INTO `myTable` (`ID`, `myGroup`, `myContent`) VALUES
(1, 2, '+=Z\\X:B|d:mK #5mc'),
(2, 2, ''),
(3, 2, 'u/RJI$SJ%T2G+|,e'),
(4, 2, 'q=7yfy''Ttb:u\\S8h];'),
(5, 2, '*J5eBaS'),
(6, 1, 'oV#Nj-,[UkF9\\i[8&'),
(7, 1, 'V Q$vyMc1'),
(8, 1, '4f{(,;MTfogK'),
(9, 1, '^f5D}'),
(10, 1, ' F'),
(11, 1, '<*b3%htX%ggh'),
(12, 2, '"u'),
(13, 2, '#|ui1z1Q@G'),
(14, 2, '7CM=JGx*9WDS}'),
(15, 1, 'u&P*$'),
(16, 1, 'XuQ?0V<1N>h<'),
(17, 1, ''),
(18, 2, 'C'),
(19, 2, 'W]9'),
(20, 1, '`9-5eZ:`Lp[./^B');
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER //;
mysql>
mysql> CREATE FUNCTION `slowFunction`(pi_ID INTEGER(11))
    ->     RETURNS int(11)
    ->     DETERMINISTIC
    ->     READS SQL DATA
    ->     SQL SECURITY DEFINER
    ->     COMMENT ''
    -> BEGIN
    ->   Select sleep(1) INTO @testtest;
    ->   SET @myCount = @myCount+1;
    ->   RETURN 1;
    -> END//;

DELIMITER ;

mysql> SET @myCount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias
    -> FROM myTable
    -> GROUP BY myGroup;
+----+---------+-------------------+-------------------+
| ID | myGroup | myContent         | slowFunctionAlias |
+----+---------+-------------------+-------------------+
|  6 |       1 | oV#Nj-,[UkF9\i[8& |                 1 |
|  1 |       2 | +=Z\X:B|d:mK #5mc |                 1 |
+----+---------+-------------------+-------------------+
2 rows in set (2.01 sec)

mysql> SELECT @myCount;
+----------+
| @myCount |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> SET @myCount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias
    -> FROM myTable
    -> GROUP BY myGroup
    -> ORDER BY slowFunctionAlias;
+----+---------+-------------------+-------------------+
| ID | myGroup | myContent         | slowFunctionAlias |
+----+---------+-------------------+-------------------+
|  6 |       1 | oV#Nj-,[UkF9\i[8& |                 1 |
|  1 |       2 | +=Z\X:B|d:mK #5mc |                 1 |
+----+---------+-------------------+-------------------+
2 rows in set (20.02 sec)

mysql>
mysql> SELECT @myCount;
+----------+
| @myCount |
+----------+
|       20 |
+----------+
1 row in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.51-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | unknown-linux-gnu            |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

sloworderby.sql

Attachment: sloworderby.sql (application/octet-stream, text), 1.38 KiB.

I can confirm your findings:

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 5
Server version: 5.1.57-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> SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias
    -> FROM myTable
    -> GROUP BY myGroup;
+----+---------+-------------------+-------------------+
| ID | myGroup | myContent         | slowFunctionAlias |
+----+---------+-------------------+-------------------+
|  6 |       1 | oV#Nj-,[UkF9\i[8& |                 1 |
|  1 |       2 | +=Z\X:B|d:mK #5mc |                 1 |
+----+---------+-------------------+-------------------+
2 rows in set (2.00 sec)

mysql> explain SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias FROM myTable GROUP BY myGroup;
+----+-------------+---------+-------+---------------+-------+---------+------+------+-------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra |
+----+-------------+---------+-------+---------------+-------+---------+------+------+-------+
|  1 | SIMPLE      | myTable | index | NULL          | Group | 5       | NULL |   20 |       |
+----+-------------+---------+-------+---------------+-------+---------+------+------+-------+
1 row in set (0.02 sec)

mysql> SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias
    -> FROM myTable
    -> GROUP BY myGroup
    -> ORDER BY slowFunctionAlias; 
+----+---------+-------------------+-------------------+
| ID | myGroup | myContent         | slowFunctionAlias |
+----+---------+-------------------+-------------------+
|  6 |       1 | oV#Nj-,[UkF9\i[8& |                 1 |
|  1 |       2 | +=Z\X:B|d:mK #5mc |                 1 |
+----+---------+-------------------+-------------------+
2 rows in set (20.02 sec)

mysql> explain SELECT ID, myGroup, myContent, slowFunction(ID) AS slowFunctionAlias FROM myTable GROUP BY myGroup ORDER BY slowFunctionAlias;
+----+-------------+---------+-------+---------------+-------+---------+------+------+---------------------------------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows | Extra                           |
+----+-------------+---------+-------+---------------+-------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | myTable | index | NULL          | Group | 5       | NULL |   20 | Using temporary; Using filesort |
+----+-------------+---------+-------+---------------+-------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

But why do you think this is a bug? As you can see above execution plans for the queries are different and your slow function is executed different number of times. Thus the difference in total time to execute statements, even though they produce the same results.

For expample: I have a table with X-Millions of rows. I group this rows and have after that only 25 rows in my resultset and I want to order that by using a function. I think it must be fast, because of the small resultset. 

In my opinion mysql have to group first and than order. But I think mysql do that reverse and that is a performance problem. First order and than grouping?!

ORDER BY is executed after GROUP BY. The problem is that IDs in your case are different in frames of the same group, and thus function(ID) may be different. When you use this MySQL extension to GROUP BY (you have scalar expressions in SELECT list that are not in GROUP BY) MySQL is free to do whatever, for example, to give you smallest function(ID) in the group, and to do this it has to select every ID, call function, put result into a temporary table and then sort it.

Read http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html:

"When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."

It's additional "service" from MySQL server for you. If you don't like it - use SQL standard GROUP BY semantics. I doubt we can claim that there is a bug here.

I agree with you it's not a bug, but a performance problem. I should use aggregate functions.

But can you explain me the different between the following selects? The secound is with a aggregate function and take more time.

mysql> SET @myCount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(ID), slowFunction(myGroup) AS Anzahl
    -> FROM myTable
    -> GROUP BY myGroup
    -> ORDER BY Anzahl;
+---------+--------+
| MAX(ID) | Anzahl |
+---------+--------+
|      20 |      1 |
|      19 |      2 |
+---------+--------+
2 rows in set (2.00 sec)

mysql> SELECT @myCount;
+----------+
| @myCount |
+----------+
|        2 |  <== ok
+----------+
1 row in set (0.00 sec)

mysql> SET @myCount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(ID), slowFunction(MAX(ID)) AS Anzahl
    -> FROM myTable
    -> GROUP BY myGroup
    -> ORDER BY Anzahl;
+---------+--------+
| MAX(ID) | Anzahl |
+---------+--------+
|      20 |      3 |
|      19 |      4 |
+---------+--------+
2 rows in set (4.00 sec)

mysql> SELECT @myCount;
+----------+
| @myCount |
+----------+
|        4 | <== i think it's not ok
+----------+
1 row in set (0.00 sec)

Sorry, I changed the Function. The Function return @myCount now.

CREATE FUNCTION `slowFunction`(
        pi_ID INTEGER(11)
    )
    RETURNS int(11)
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  Select sleep(1) INTO @testtest;
  SET @myCount = @myCount+1;
  RETURN @myCount;
END;

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.