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

[6 Oct 2008 14:22] Armin Fiedler
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)
[6 Oct 2008 14:47] Valeriy Kravchuk
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.
[7 Oct 2008 12:07] Armin Fiedler
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.