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
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;
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;
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;