Xrhrft

Hi i'm currently trying to run a SQL query on a 15 000 + entry dataset. I'm using a custom Mysql function to determine the start of the current week and the end of it. Probleme is, I'm using those values in the WHERE clause of my query so it is executed at each comparaison, which take a lot of time.
I've discovered that those values never change, so I need to execute the function only one time to get the value, I can then pass the value to the WHERE clause.


The function itself does not takes long to execute, it's doing it 60 000 times that takes a long time.


I was wondering if there is a way to save those values into some kind of table where I could check if the start and end of the week has already been calculated and if so, use those value.

I tried using a temporary table but I would need to know when the query is done so I could delete it.

Here is my code so far :

SELECT TIME_TO_SEC(TIMEDIFF(STR_TO_DATE(otherShifts.sft_end, '%Y-%m-%dT%H:%i:%s.000Z'), 

                            STR_TO_DATE(otherShifts.sft_start, '%Y-%m-%dT%H:%i:%s.000Z'))) / 3600 as timestamps

                    FROM shift_sft as otherShifts,

                         shift_sft as originalShift

                    WHERE (

                        otherShifts.sft_start BETWEEN getStartOfWeek(originalShift.sft_id) AND getEndOfWeek(originalShift.sft_id) OR

                        otherShifts.sft_end BETWEEN getStartOfWeek(originalShift.sft_id) AND getEndOfWeek(originalShift.sft_id))

                    AND originalShift.sft_id = 1;

getStartOfWeek :

create function getStartOfWeek(shiftId int) returns varchar(122)

BEGIN

      DECLARE shiftStart varchar(122);

      DECLARE currentUserId INT(11);

      DECLARE currentSunday DATETIME;

      DECLARE currentWeekStart DATETIME;

      DECLARE currentStartingWeekSetting int;

      SELECT sft_start, sft_usr_id INTO shiftStart, currentUserId FROM shift_sft WHERE sft_id = shiftId;

      SET currentSunday :=  DATE_ADD(STR_TO_DATE(SUBSTRING_INDEX(shiftStart, 'T', 1), '%Y-%m-%d'), INTERVAL(1-DAYOFWEEK(STR_TO_DATE(SUBSTRING_INDEX(shiftStart, 'T', 1),  '%Y-%m-%d'))) DAY);

       SELECT FIELD(sde_day_week_start,'sun','mon', 'tue', 'wen', 'thu', 'fri', 'sat')

       FROM setting_default_sde, setting_business_sbu, v_user_business

       WHERE v_user_business.usr_id = 1

       AND setting_business_sbu.sbu_sde_id = setting_default_sde.sde_id

       AND setting_business_sbu.sbu_bus_id = v_user_business.bus_id;

       SET currentWeekStart := DATE_ADD(currentSunday, INTERVAL (currentStartingWeekSetting - 1) DAY);

       RETURN DATE_FORMAT(currentWeekStart, '%Y-%m-%d');

     END;

getEndOfWeek:

create function getEndOfWeek(shiftId int) returns varchar(122)

BEGIN

   DECLARE startOfWeek varchar(122);

   DECLARE endOfWeek DATETIME;

   SELECT getStartOfWeek(shiftId) INTO startOfWeek;

   SET endOfWeek := DATE_ADD(startOfWeek, INTERVAL 7 DAY);

   RETURN DATE_FORMAT(endOfWeek, '%Y-%m-%d');

 END;

Build a table with every day for the next few years:

 CREATE TABLE foo (

     dy DATE NOT NULL,   -- every day

     bow DATE NOT NULL,  -- date (yyyy/mm/dd) of the Beginning Of Week

     eow DATE NOT NULL,

     PRIMARY KEY(dy)

 ) ENGINE=InnoDB;

Then, instead of having a function, JOIN to that table to fetch the bow and/or eow needed.

Meanwhile, DATE_FORMAT(some_date, '%a') yields Sun, Mon, etc. And WEEKDAY(some_date) gives you 0 for Monday, etc. These might simplify some of your code.