MySQL Bugs: #34310: Optimizer is inefficient by using ORDER BY function() and LIMIT

Bug #34310	Optimizer is inefficient by using ORDER BY function() and LIMIT
Submitted:	5 Feb 2008 14:51	Modified:	18 Dec 2013 14:03
Reporter:	Richard Teubel	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.51a-log, 5.0.54, 5.6.14	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	FUNCTION, limit, order by, query
Triage:	Triaged: D3 (Medium)

Description:
Hello,

i think the optimizer is inefficient by using ORDER BY function() and LIMIT. In my Table are 10 rows. func_A receive only the parameter. func_B receive it, too. But it's a slow function which sleep 100 ms and increment @testcount. If I use ORDER BY func_A and say LIMIT 1,2 for 2 rows then func_B has 10 passes with 1000 ms and not 2 passes with 200ms.

First I show you the Query with Order By a TableColumn and the func_B has only 2 passes.

SET @testcount = 0;

SELECT A.Value AS Order1,
       func_A(A.Value) AS Order2,
       func_B(A.Value) AS Order3
FROM A
ORDER BY Order1
LIMIT 1,2;

+------------------+------------------+------------------+
| Order1           | Order2           | Order3           |
+------------------+------------------+------------------+
| )B7:             | )B7:             | )B7:             |
| 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n |
+------------------+------------------+------------------+
2 rows in set (0.22 sec)  <<<<===== 2rows, 200 ms it's Ok

SELECT @testcount;
+------------+
| @testcount |
+------------+
| 2          |
+------------+
1 row in set (0.00 sec)

Now, the same Query with ORDER BY func_A(A.Value) and the func_B has 10 passes. Very bad, it take 1000 ms.

SET @testcount = 0;

SELECT A.Value AS Order1,
       func_A(A.Value) AS Order2,
       func_B(A.Value) AS Order3
FROM A
ORDER BY Order2
LIMIT 1,2;
+------------------+------------------+------------------+
| Order1           | Order2           | Order3           |
+------------------+------------------+------------------+
| )B7:             | )B7:             | )B7:             |
| 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n |
+------------------+------------------+------------------+
2 rows in set (1.04 sec) <<<<===== 2 rows, 1000 ms :(

SELECT @testcount;
+------------+
| @testcount |
+------------+
| 10         |  <<=== 8 passes too much.
+------------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE IF NOT EXISTS `A` (
  `ID` int(11) unsigned NOT NULL auto_increment,
  `Value` varchar(20) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `A` (`ID`, `Value`) VALUES
(1, 'Q;ls(#/''VE:KgC#^S'),
(2, ' Dqs#._'),
(3, 'G!c'),
(4, 'W"(l?P'),
(5, '24KrL5PL<7JVJ`.n'),
(6, 'F<FgmKWY'),
(7, ':u]qu4=re2{b'),
(8, 'iJvIq7Va'),
(9, ')B7:'),
(10, 'faC.]+Yw>VEc$F');

DELIMITER //

CREATE DEFINER=`root`@`%` FUNCTION `func_A`(param1 varCHAR(20)) RETURNS varchar(20) CHARSET latin1
    DETERMINISTIC
BEGIN
  RETURN param1;
END //

CREATE DEFINER=`root`@`%` FUNCTION `func_B`(param1 varCHAR(20)) RETURNS varchar(20) CHARSET latin1
    DETERMINISTIC
BEGIN
  SELECT SLEEP(0.1) INTO @temp;
  
  SET @testcount = @testcount + 1;
  
  RETURN param1;
END //

DELIMITER ;

--
--

SET @testcount = 0;

SELECT A.Value AS Order1,
       func_A(A.Value) AS Order2,
       func_B(A.Value) AS Order3
FROM A
ORDER BY Order1
LIMIT 1,2;

SELECT @testcount;

--
--

SET @testcount = 0;

SELECT A.Value AS Order1,
       func_A(A.Value) AS Order2,
       func_B(A.Value) AS Order3
FROM A
ORDER BY Order1
LIMIT 1,2;

SELECT @testcount;

Thank you for a problem report. Verified just as described also with 5.0.54:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE IF NOT EXISTS `A` (
    ->   `ID` int(11) unsigned NOT NULL auto_increment,
    ->   `Value` varchar(20) NOT NULL,
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> INSERT INTO `A` (`ID`, `Value`) VALUES
    -> (1, 'Q;ls(#/''VE:KgC#^S'),
    -> (2, ' Dqs#._'),
    -> (3, 'G!c'),
    -> (4, 'W"(l?P'),
    -> (5, '24KrL5PL<7JVJ`.n'),
    -> (6, 'F<FgmKWY'),
    -> (7, ':u]qu4=re2{b'),
    -> (8, 'iJvIq7Va'),
    -> (9, ')B7:'),
    -> (10, 'faC.]+Yw>VEc$F');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> delimiter //
mysql> CREATE DEFINER=`root`@`%` FUNCTION `func_A`(param1 varCHAR(20)) RETURNS v
archar(20)
    -> CHARSET latin1
    ->     DETERMINISTIC
    -> BEGIN
    ->   RETURN param1;
    -> END //
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE DEFINER=`root`@`%` FUNCTION `func_B`(param1 varCHAR(20)) RETURNS varchar(20)
    -> CHARSET latin1
    ->     DETERMINISTIC
    -> BEGIN
    ->   SELECT SLEEP(0.1) INTO @temp;
    ->
    ->   SET @testcount = @testcount + 1;
    ->
    ->   RETURN param1;
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> SET @testcount = 0;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT A.Value AS Order1,
    ->        func_A(A.Value) AS Order2,
    ->        func_B(A.Value) AS Order3
    -> FROM A
    -> ORDER BY Order1
    -> LIMIT 1,2;
+------------------+------------------+------------------+
| Order1           | Order2           | Order3           |
+------------------+------------------+------------------+
| )B7:             | )B7:             | )B7:             |
| 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n |
+------------------+------------------+------------------+
2 rows in set (0.25 sec)

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

mysql> explain SELECT A.Value AS Order1,
    ->        func_A(A.Value) AS Order2,
    ->        func_B(A.Value) AS Order3
    -> FROM A
    -> ORDER BY Order1
    -> LIMIT 1,2;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |   10
 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
1 row in set (0.00 sec)

mysql> SET @testcount = 0;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> SELECT A.Value AS Order1,
    ->        func_A(A.Value) AS Order2,
    ->        func_B(A.Value) AS Order3
    -> FROM A
    -> ORDER BY Order2
    -> LIMIT 1,2;
+------------------+------------------+------------------+
| Order1           | Order2           | Order3           |
+------------------+------------------+------------------+
| )B7:             | )B7:             | )B7:             |
| 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n | 24KrL5PL<7JVJ`.n |
+------------------+------------------+------------------+
2 rows in set (1.11 sec)

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

mysql> explain SELECT A.Value AS Order1,
    ->        func_A(A.Value) AS Order2,
    ->        func_B(A.Value) AS Order3
    -> FROM A
    -> ORDER BY Order2
    -> LIMIT 1,2;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+---------------------------------+
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |   10
 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+---------------------------------+
1 row in set (0.00 sec)

I think EXPLAIN shows the difference (Using temporary in second case), but I still do not understand why for DETERMINISTIC functions there is any difference...

This problem exists allready in version 5.6.14.