Re: Slow query when where clause contains function

[Øystein Grøvlen]

Hi,

You need to declare your function as deterministic in order for an index to be used.
(Otherwise, the optimizer will not know whether the function would return the same
value for every row or not.)

Example:

mysql> DELIMITER $$
mysql> CREATE FUNCTION same(txt VARCHAR(111))
-> RETURNS VARCHAR(111)
-> BEGIN
-> RETURN txt;
-> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION same_det(txt VARCHAR(111))
-> RETURNS VARCHAR(111)
-> DETERMINISTIC
-> NO SQL
-> BEGIN
-> RETURN txt;
-> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CREATE TABLE tablea (col1 VARCHAR(111) PRIMARY KEY, col2 int);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO tablea VALUES ('a', 1), ('b', 2);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> EXPLAIN select * from tablea where same(col1) = 'a';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tablea | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from tablea where col1 = same('a');
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tablea | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from tablea where col1 = same_det('a');
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | tablea | const | PRIMARY | PRIMARY | 113 | const | 1 | NULL |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway