Mysql Stored Function Performance Issue

Question

I have a query which runs very fast as itself, but when I use that query as a function's body it suffers a great slowdown. 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 query statements;

first, the query runs 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 roughly in 0.03 seconds while @id2 arrives in 3.5 seconds (2 seconds at best). I wonder what causes this remarkable performance hit.

I need to use this function in another stored procedure, thus waiting 2-3 seconds for each row in the stored procedure kills my total performance.

Can anybody help me improve from this point?

@newtover: because query timestamps are logged in the binary log — TehShrike
@Abdullah Battal: could you include the SHOW CREATE TABLE results for the Schedule table, and maybe even pastebin some example data in INSERT queries for testing? — TehShrike
What does explain say? How it performs the query when tell SELECT SQL_NO_CACHE .... ? — Pentium10
Does this function always return the same result for a given prog_id? If not, it is not deterministic. Mis-declaring it can cause issues with the results and performance. — John Watson

TehShrike TehShrike · Accepted Answer · 2011-11-18T16:09:29

Without access to a good set of test data, it's hard to play around with this much. I have only a few suggestions (coughguessescough) for things you could try changing.

Explicitly declare character type for the function parameter

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

Just return the subquery in 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 was able to create the function with the above code, and it worked fine under MySQL 5.1.45. For accurate testing, your queries need this line, or you can't really trust the numbers you get back to find out how expensive your queries are.

Sacrifice a chicken to the RDBMS deities

That's all I've got for now - I'm curious about this issue, so if you'd be willing to paste some testing data somewhere so that I could experiment more, I'd be willing to do so.

Feel free to ping me in the MySQL chat room if you want to chew the fat about this funky-sounding problem.