**BACKGROUND**

I am working with a high traffic application which seems is extremely slow when executing the following. 

Below is a description of my problem:

I have the following function defined:

```
CREATE FUNCTION getTableXMax() RETURNS INT
BEGIN
DECLARE NUM INT DEFAULT 0;
SELECT COALESCE((SELECT MAX(ID) FROM TABLE_X),0) INTO NUM;
RETURN NUM;
END //

```

TABLE\_X has more than 30 million entries.

**PROBLEMATIC QUERY**

```
mysql> UPDATE TABLE_X SET COST = 0 WHERE ID=49996728;
    -> //
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

```

```
mysql> UPDATE TABLE_X SET COLUMN_X=0 WHERE ID=getTableXMax();
    -> //
Query OK, 1 rows affected (1 min 23.13 sec)
Rows matched: 1  Changed: 0  Warnings: 0

```

**------- QUESTION -----------**

As you can see above, the problem is that the query below takes more than a minute to execute when using the mysql function. I want to understand why this happens (although overall implementation might be bad).

**------- DEBUG --------------**

I run some EXPLAIN queries to check the possible\_keys that mysql uses in order to perform the search. As you can see below the query that uses the function has a NULL value for possible\_keys - thus I assume the why the problem exists is probably answered. The questions remaining is how to fix it, and what is the reason.

```
mysql> EXPLAIN UPDATE TRANSCRIPTIONS SET COST = 0 WHERE ID=12434;//
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | TRANSCRIPTIONS | range | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

```

```
mysql> EXPLAIN UPDATE TRANSCRIPTIONS SET COST = 0 WHERE ID=getTableXMax();//
+----+-------------+----------------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | TRANSCRIPTIONS | index | NULL          | PRIMARY | 4       | NULL | 38608423 | Using where |
+----+-------------+----------------+-------+---------------+---------+---------+------+----------+-------------+

```

**MYSQL VERSION**

```
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.34                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.34                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

```

I hope my question was thorough enough.