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: | |
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
[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.