MySQL Bugs: #39883: Performance problem with stored function in where clause

Bug #39883	Performance problem with stored function in where clause
Submitted:	6 Oct 2008 14:22	Modified:	7 Oct 2008 12:07
Reporter:	Armin Fiedler	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.51a / 5.0.67	OS:	Linux (opensuse 11.0)
Assigned to:		CPU Architecture:	Any

Description:
In my productive system, a select statement on a table with a function call (which in turn includes a select statement on another table) in the where clause takes 0.13 sec in MySQL 5.0.18, but takes 5 min 49.20 sec in MySQL 5.0.51a-Max

I simplified the problem to the following example (using one table only), which takes virtually no time (0.00 sec) in MySQL 5.0.18, but 1.35 sec in MySQL 5.0.67.

How to repeat:
I simplified the problem to the following example (using one table only), which takes virtually no time (0.00 sec) in MySQL 5.0.18, but 1.35 sec in MySQL 5.0.67.

DROP TABLE IF EXISTS `Example`;
CREATE TABLE `Example` (
  `day` date NOT NULL default '0000-00-00',
  `id` int(10) NOT NULL default 0,
  `data` int(10) unsigned default NULL,
  PRIMARY KEY  (`day`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

DROP FUNCTION IF EXISTS `LastDay`;
CREATE FUNCTION LastDay () RETURNS DATE
RETURN (SELECT MAX(day) FROM Example);

-- I define a function to enter a huge amount of data
DROP FUNCTION IF EXISTS `enter_data`;
delimiter //
CREATE FUNCTION enter_data (m INT, n INT, d DATE) RETURNS INT
BEGIN
  DECLARE r INT DEFAULT n;
  WHILE (m > 0) DO
    set n = r;
    WHILE (n > 0) DO
        insert into Example (day, id, data) values (d, n, m+n);
    	set n = n - 1;
    END WHILE;
    set d = adddate(d,1);
    set m = m - 1;
  END WHILE;
  SELECT COUNT(*) FROM Example INTO r;
  RETURN r;
END;
//
delimiter ;
-- now I enter the data
select enter_data(1000,100,'2000-01-01');

Here is what happens in 5.0.18-standard:

mysql> select count(*) from Example where day = LastDay();
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from Example where day = LastDay()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Example
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 62
        Extra: Using index
1 row in set (0.00 sec)

mysql>

Here is what happens in 5.0.67:

mysql> select count(*) from Example where day = LastDay();
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (1.35 sec)

mysql> explain select count(*) from Example where day = LastDay()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Example
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 100000
        Extra: Using where; Using index
1 row in set (0.00 sec)

You just have to add DETERMINISTIC clause in recent 5.0.x:

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 6
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> DROP TABLE IF EXISTS `Example`;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE TABLE `Example` (
    ->   `day` date NOT NULL default '0000-00-00',
    ->   `id` int(10) NOT NULL default 0,
    ->   `data` int(10) unsigned default NULL,
    ->   PRIMARY KEY  (`day`,`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
Query OK, 0 rows affected (0.14 sec)

mysql> DROP FUNCTION IF EXISTS `LastDay`;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CREATE FUNCTION LastDay () RETURNS DATE
    -> RETURN (SELECT MAX(day) FROM Example);
Query OK, 0 rows affected (0.06 sec)

mysql> DROP FUNCTION IF EXISTS `enter_data`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> CREATE FUNCTION enter_data (m INT, n INT, d DATE) RETURNS INT
    -> BEGIN
    ->   DECLARE r INT DEFAULT n;
    ->   WHILE (m > 0) DO
    ->     set n = r;
    ->     WHILE (n > 0) DO
    ->         insert into Example (day, id, data) values (d, n, m+n);
    ->          set n = n - 1;
    ->     END WHILE;
    ->     set d = adddate(d,1);
    ->     set m = m - 1;
    ->   END WHILE;
    ->   SELECT COUNT(*) FROM Example INTO r;
    ->   RETURN r;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select enter_data(1000,100,'2000-01-01');
+-----------------------------------+
| enter_data(1000,100,'2000-01-01') |
+-----------------------------------+
|                            100000 |
+-----------------------------------+
1 row in set (2.67 sec)

mysql> select count(*) from Example where day = LastDay();
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (1.66 sec)

mysql> explain select count(*) from Example where day = LastDay()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Example
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 7
          ref: NULL
         rows: 100000
        Extra: Using where; Using index
1 row in set (0.01 sec)

Now, let's help optimizer to make correct decision:

mysql> drop function LastDay;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE FUNCTION LastDay () RETURNS DATE
    -> DETERMINISTIC RETURN (SELECT MAX(day) FROM Example);
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from Example where day = LastDay()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Example
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 62
        Extra: Using index
1 row in set (0.01 sec)

and query will run instantly according to this plan:

mysql> select count(*) from Example where day = LastDay();
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

This is NOT a bug. Please, read http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html.

Thanks for the quick reply.  I had read about the DETERMINISTIC keyword, but the documentation says: 

'A procedure or function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.'

Since the function accesses a table (and might return a different value tomorrow), I thought it is to be considered a not deterministic, even though the return value will not change during the select call.