Problem with saved mysql function

Question

I have a query that runs very quickly, like it is, but when I use this query as a function body, it slows down a lot. Here is my test case:

/******************* my function definition *********************/ DELIMITER $$ CREATE DEFINER=`root`@`%` FUNCTION `GetNextScheduleForProgram`( prog_id varchar(10) ) RETURNS varchar(10) CHARSET latin5 DETERMINISTIC BEGIN DECLARE scheduleid varchar(10); SET scheduleid = ( SELECT sc.ScheduleID FROM Schedule sc WHERE sc.ProgramID=prog_id AND sc.StartDate BETWEEN now() and date_add(now(), interval 3 day) ORDER BY sc.StartDate ASC LIMIT 1 ); RETURN scheduleid; END 

And here are the query instructions:

• firstly, the request is executed as itself
• then the function is used with the same parameter:

  SET @ id1 = (SELECT sc.ScheduleID
               FROM Schedule sc
              WHERE sc.ProgramID = '23860'
                AND sc.StartDate BETWEEN now () and date_add (now (), interval 3 day)
           ORDER BY sc.StartDate ASC
              LIMIT 1);
 SET @ id2 = GetNextScheduleForProgram ('23860');

In this test, @ id1 is set to about 0.03 seconds , and @ id2 after 3.5 seconds (at best, 2 seconds). I wonder why this wonderful performance hit.

I need to use this function in another stored procedure, waiting 2-3 seconds for each line of the stored procedure to kill my full performance.

Can someone help me improve the situation from now on?

Answer 1

Without access to a good set of test data, this is difficult to play with. I have only a few suggestions (* cough * guesses * cough *) for things you could try changing.

Explicitly declare character type for function parameter

 CREATE DEFINER=`root`@`%` FUNCTION `GetNextScheduleForProgram`( prog_id varchar(10) CHARSET latin5 ... 

Just return the subquery to the function

 BEGIN RETURN ( SELECT SQL_NO_CACHE sc.ScheduleID FROM Schedule AS sc WHERE sc.ProgramID = prog_id AND sc.StartDate BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 3 DAY) ORDER BY sc.StartDate ASC LIMIT 1 ); END 

For accurate testing, use SQL_NO_CACHE

I managed to create a function with the above code, and it worked perfectly under MySQL 5.1.45. For accurate testing, your queries need this line, or you cannot really trust the numbers you return to find out how expensive your queries are.

Donate chicken to the RDBMS gods

That's all I have at the moment - I'm curious about this problem, so if you want to insert some test data somewhere so that I can experiment more, I would be ready to do it.

Feel free to ping me on MySQL chat if you want to chew on fat on this problematic issue.