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