MySQL function execution time increases everytime I SELECT it

Question

Bit of a strange one. I have a function that runs perfectly fine at first, say 15ms execution and 10ms fetching when selecting the returned value plus some other columns. But if keep refreshing the same query, over and over, the execution of the query goes up. So first it's 15ms, then 17, then... I got it all the way to 900ms. It's mostly the fetching that goes up in time, but the execution too. So at the end it'll be 600ms for fetching and 300ms for execution. Any ideas what's going on?

Function. I experimented with just a simple IF/ELSEIF but it gives the same exact result in terms of performance.

create function get_table(var_account_id int unsigned) returns varchar(20)
    reads sql data
BEGIN
    RETURN IF(
            (SELECT EXISTS(SELECT TRUE
                           FROM TableA
                           WHERE account_id = var_account_id
                             AND expiring_at > CURRENT_TIMESTAMP)), 'TableA',
            IF((SELECT EXISTS(SELECT TRUE
                              FROM TableB
                              WHERE account_id = var_account_id
                                AND expiring_at > CURRENT_TIMESTAMP)), 'TableB',
               IF((SELECT EXISTS(SELECT TRUE
                                 FROM TableC
                                 WHERE account_id = var_account_id
                                   AND expiring_at > CURRENT_TIMESTAMP)), 'TableC',
                  IF((SELECT EXISTS(SELECT TRUE
                                    FROM TableD
                                    WHERE account_id = var_account_id
                                      AND expiring_at > CURRENT_TIMESTAMP)), 'TableD',
                     NULL)
                   )));
END;

Explain of function after running it once with var_account_id = 1

9,SUBQUERY,TableD,,ref,"TableD_expiring_at_index,TableD_account_id_index",TableD_account_id_index,4,const,1,100,Using where
7,SUBQUERY,TableC,,ref,"TableC_account_id_index,TableC_expiring_at_index",TableC_account_id_index,4,const,1,5,Using where
5,SUBQUERY,TableB,,ref,"TableB_expiring_at_index,TableB_account_id_index",TableB_account_id_index,4,const,1,9.26,Using where
3,SUBQUERY,TableA,,ref,"TableA_expiring_at_index,TableA_account_id_index",TableA_account_id_index,4,const,1,100,Using where

Putting a compound index on account_id and expiring_at has no effect at all

And I run a simple query like

SELECT TableXYZ.*, get_table(TableXYZ.account_id) AS some_value FROM TableXYZ LIMIT 500;

I've run it on more complicated queries but the result is always the same, fast at first, slow after rerunning the same SELECT let's say 5 times a second for 30 secs straight. Even after I let MySQL cool off for a bit, come back, and the first run is still 900ms. And I'm sure it can keep going up. The only way to fix this is restarting the mysql service in windows.

Explain of the SELECT:

1,SIMPLE,TableXYZ,,ALL,,,,,695598,100,

I'm running these on Windows 10 if it matters, localy.

Very weird indeed. Could it be the OS is somehow throttling the database service? (that should make other queries slower over time too I guess) — deroby, Apr 29 '21 at 07:15
Weird! What MySQL version? How big are the tables a,b,c,d, e? Please [edit] your question. — O. Jones, Apr 29 '21 at 13:26

Michael Warreyn · Answer 1 · 2021-04-28T20:45:05.150

Sounds crazy. Maybe you can avoid the subqueries, which often cause performance problems

SELECT X.tabName
FROM (
SELECT 'TableA' as tabName, 1 as tabNr, account_id, expiring_at FROM TableA WHERE account_id = var_account_id AND expiring_at > CURRENT_TIMESTAMP
UNION
SELECT 'TableB' as tabName, 2 as tabNr, account_id, expiring_at FROM TableB WHERE account_id = var_account_id AND expiring_at > CURRENT_TIMESTAMP
UNION
...
    ) X ORDER BY tabNr LIMIT 1

If you want to avoid a union because you have very big tables, then why not use control flow in the function?

DECLARE tabName VARCHAR(50);
SET tabName := SELECT 'TableA' FROM TableA WHERE account_id = var_account_id AND expiring_at > CURRENT_TIMESTAMP;

IF (tabName IS NULL) THEN
   SET tabName := SELECT 'TableB' FROM TableB WHERE account_id = var_account_id AND expiring_at > CURRENT_TIMESTAMP;
END IF;

and so on...

RETURN tabName;

I've tried the first one and although the time doesn't go up over time, it's slow, so it starts at 1.3 seconds, where as calling the function (with the same exact code pretty much) does it in 300ms, but then it starts going up. The 2nd way I've also tried. Same performance, but it goes up over time. Same as the code in my post. I'm lost, I've never seen this happen before. — Damian, Apr 28 '21 at 21:35

score 0 · Answer 2 · answered May 01 '21 at 00:50

RETURN COALESCE(
    IF (EXISTS(...), "A", NULL),
    IF (EXISTS(...), "B", NULL),
    IF (EXISTS(...), "C", NULL),
    IF (EXISTS(...), "D", NULL),
    IF (EXISTS(...), "E", NULL)
               )

where ... is, for example:

SELECT 1
    FROM TableA
    WHERE account_id = var_account_id
      AND expiring_at > CURRENT_TIMESTAMP

Notes:

Be sure to have this index in table*: INDEX(account_id, expired_at) (in that order).
EXISTS() stops where a matching row is found. (One proposed solution failed to stop, hence took longer.)
COALESCE() does not need to evaluate all of its arguments. (UNION does.)
(No, I don't see why it would get slower and slower. Hopefully, my formulation will be consistently faster.)

MySQL function execution time increases everytime I SELECT it

2 Answers2