Slow stored MySQL function gets progressively slower with repeated runs

user_449692 0
Viewed 144

I need to filter a list by whether the person has an appointment. This runs in 0.09 seconds.

select personid from persons p
where EXISTS (SELECT 1 FROM appointments a
                    WHERE a.personid = p.personid);

Since I use this in more than one query and it actually contains another condition, it seemed convenient to put the filter into a function, so I have

CREATE FUNCTION `has_appt`(pid INT) RETURNS tinyint(1)
BEGIN
RETURN 
    EXISTS (SELECT 1 FROM appointments WHERE personid = pid);
END

Then I can use

select personid from persons where has_appt(personid)

However, two unexpected things happen. First, the statement using the has_appt() function now takes 2.5 seconds to run. I know there is overhead to a function call, but this seems extreme. Second, if I run the statement repeatedly, it takes about 5 seconds longer each time, so by the 4th time, it is taking over 20 seconds. This happens regardless of how long I wait between tries, but storing the function again resets the time to 2.5 seconds. What can account for the progressive slowness? What state can be affected by simply running it multiple times?

I know the solution is to forget the function and just embed this into my queries, but I want to understand the principle so I can avoid making the same mistake again. Thanks in advance for you help.

I'm using MySQL 8 and Workbench.

stored-functions optimization mysql performance
1 Answers

Your original query can be replaced by, and sped up by,

SELECT personid FROM appointments;

But the query seems dumb -- why would you want a list of all it ids of people with appointments, but no info about them? Perhaps you over-simplified the query?

If a person might have multiple appointments, then this would be needed, and might not be as fast:

SELECT DISTINCT personid FROM appointments;

As for why the function is so slow... Optimization does not see what is inside the function. So select personid from persons where has_appt(personid) walks through the entire persons table, calling the function repeatedly.

user_17668310
Related