CODEWITHSUNDEEP
mysqlfunction
Denis Palnitsky
Denis Palnitsky

Reputation: 18387

MySql function not using indexes

I have simple function consist of one sql query

CREATE FUNCTION `GetProductIDFunc`( in_title char (14) )
        RETURNS bigint(20)
BEGIN
  declare out_id bigint;    

  select id into out_id from products where title = in_title limit 1;    
  RETURN out_id;
END

Execution time of this function takes 5 seconds

select Benchmark(500 ,GetProductIdFunc('sample_product'));

Execution time of plain query takes 0.001 seconds

select Benchmark(500,(select id from products where title = 'sample_product' limit 1));

"Title" field is indexed. Why function execution takes so much time and how can I optimize it?

edit: Execution plan

mysql> EXPLAIN EXTENDED select id from products where title = 'sample_product' limit 1;
+----+-------------+----------+-------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+-------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | products | const | Index_title   | Index_title | 14      | const |    1 |   100.00 | Using index |
+----+-------------+----------+-------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN select GetProductIdFunc('sample_product');
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Upvotes: 2

Views: 768

Answers (2)

ceteras
ceteras

Reputation: 3378

Try this:

CREATE FUNCTION `GetProductIDFunc`( in_title char (14) )
        RETURNS bigint(20)
BEGIN
  declare out_id bigint;    

  set out_id = (select id from products where title = in_title limit 1);    
  RETURN out_id;
END

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65577

This could be a character set issue. If the function is using a different character set than the table column, it would lead to very slow performance despite the index.

Run show create table products\G to determine the character set for the column.

Run show variables like 'character_set%'; to see what the relevant default character sets are for your DB.

Upvotes: 2

Related Questions