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.
I think that
UPDATE TABLE_X
SET COLUMN_X=0
ORDER BY ID DESC
LIMIT 1
is enough. And the function is not needed at all.
If you want to save the function and the logic then use
UPDATE TABLE_X,
( SELECT getTableXMax() criteria ) fn
SET COLUMN_X=0
WHERE ID=criteria;
But as the first step - try to define the function as DETERMINISTIC.