MySql function not using indexes

Question

Hi. 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)

Answer 1

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.

Answer 2

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