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: | |
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 |
[5 Feb 2008 14:51]
Richard Teubel
[5 Feb 2008 18:24]
Valeriy Kravchuk
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...
[18 Dec 2013 14:03]
Richard Teubel
This problem exists allready in version 5.6.14.