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.

Answer 1

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;

Answer 2

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