Bug #39188 Usage of stored function in where clause slows query
Submitted: 2 Sep 2008 14:14 Modified: 16 Oct 2008 10:19
Reporter: Eugen Schülter Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.67-community-nt, 5.1.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: 36713, slow, SP, stored function

[2 Sep 2008 14:14] Eugen Schülter
Description:
Seems to be, that bug 36713 is present again.

A query calling a stored function in the where clause is very slow. Present also in the current debian version (5.0.51a-9+lenny2-log).

In my tiny sample provided the query takes 0.41 sec compared to 0.00 sec using a user variable.

With our 'real' functions queries execute in ~ 2 sec compared to some milliseconds.

Not a serius issue but very strange to me.

How to repeat:
/* complete sample showing the 'error' */

DELIMITER //

DROP PROCEDURE IF EXISTS pCreateSampTables//

CREATE PROCEDURE pCreateSampTables()
LANGUAGE SQL
BEGIN
   DECLARE i INT DEFAULT 0;

   DROP TABLE IF EXISTS tS1, tS2;
   CREATE TABLE tS1 (i1 INTEGER PRIMARY KEY);
   CREATE TABLE tS2 (ai INTEGER AUTO_INCREMENT PRIMARY KEY, i2 INTEGER);

   WHILE i < 10000 DO
      SET i = i+1;
      INSERT INTO tS1 VALUES(i);
      INSERT INTO tS2 VALUES(NULL, i);
   END WHILE;
END;
//

CALL pCreateSampTables()//

DROP FUNCTION IF EXISTS fTest//

CREATE FUNCTION fTest(inp INTEGER) RETURNS INTEGER
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
   DECLARE res INTEGER;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET res = 0;

   SELECT i1 INTO res FROM tS1 WHERE i1 < inp ORDER BY i1 DESC LIMIT 1;

   RETURN res;
END;
//

DELIMITER ;

SET @a=fTest(490);

SELECT * FROM tS2 WHERE i2=@a;

SELECT * FROM ts2 WHERE i2=fTest(490);

/* output:
mysql> SET @a=fTest(490);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM tS2 WHERE i2=@a;
+-----+------+
| ai  | i2   |
+-----+------+
| 489 |  489 |
+-----+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT * FROM ts2 WHERE i2=fTest(490);
+-----+------+
| ai  | i2   |
+-----+------+
| 489 |  489 |
+-----+------+
1 row in set (0.41 sec)

*/
[2 Sep 2008 14:55] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[2 Sep 2008 14:57] Valeriy Kravchuk
EXPLAIN shows no difference:

mysql> explain extended SELECT * FROM tS2 WHERE i2=@a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tS2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8551
        Extra: Using where
1 row in set, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`ts2`.`ai` AS `ai`,`test`.`ts2`.`i2` AS `i2` from `test`.
`ts2` where (`test`.`ts2`.`i2` = (@a))
1 row in set (0.00 sec)

mysql> explain extended SELECT * FROM ts2 WHERE i2=fTest(490)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8551
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`ts2`.`ai` AS `ai`,`test`.`ts2`.`i2` AS `i2` from `test`.
`ts2` where (`test`.`ts2`.`i2` = `fTest`(490))
1 row in set (0.00 sec)

but I assume function is just called for each and every row accessed.
[2 Sep 2008 15:20] Valeriy Kravchuk
5.1 is also affected.
[15 Oct 2008 12:06] Peter Bauwens
I have the same problem. 
After research with different versions, MySQL v5.0.24 is not yet affected, but v5.0.45 definitly is. I found that possibly bugfix for bug 27354 is the reason of this issue. I didn't test with versions between 5.0.24 and 5.0.41.

If I use the 'EXPLAIN' (with the code I used below) I see differences between the different versions:
Version  type  poss_keys key     rows
5.0.24   ref   PRIMARY   PRIMARY 1
5.0.41   const PRIMARY   PRIMARY 1 (with bug#27354)
5.0.45   ALL   NULL      NULL    3 
Higher versions have the same result as version 5.0.45

The code I tested with:

CREATE TABLE test (
  Col1 varchar(4) NOT NULL,
  Col2 varchar(5) NOT NULL,
  PRIMARY KEY  (Col1)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO test (Col1, Col2) VALUES ('1', ''), ('2', ''), ('3', '');

CREATE FUNCTION SimpleFunction(inSelection VARCHAR(4)) RETURNS CHAR(2) READS SQL DATA
BEGIN
  RETURN inSelection;
END;

EXPLAIN
SELECT *
FROM test
WHERE Col1 = SimpleFunction('1');
[16 Oct 2008 10:19] Sergei Golubchik
MySQL can only optimize SimpleFunction('1') call and perform it once, if it knows for sure that this optimization won't change the result of the query, that is, if all calls of SimpleFunction('1') will return the same result.

And MySQL doesn't know it, unless you tell so, your function is deterministic, so you need to use DETERMINISTIC keyword when declaring it. Then MySQL could figure out that calling a deterministic function with the same argument will always produce the same result, and it could optimize extra calls out.
[16 Oct 2008 12:08] Peter Bauwens
The SimpleFunction('1') was an abnormal simplification of an other normal function, only to demonstrate and test the problem with as little code as possible.
With v5.0.24 there was no problem, good results and good performance, v5.0.45 and higher were very slow (>20 times slower) with the original function (which is a non-deterministic one).