Find free appointments faster
Hi there,
I'm currently working on a calendar scheduling system and would like to optimize the performance while calculating free timeslots. There are various conditions for finding free timeslots - there may exist multiple hourly starting times for example, the appointments may overlap or happen simultaneously, so I'm not totally sure if calculating the timeslots with complex conditions within the database is the best way in my case. My query needs about 50 ms. This does not seem to be much at a first glance, but I need to calculate the appointments for more than one day and a group of people in different rooms which each may have multiple openingtimes for a single day. Because of this I can end up with many queries and a total of several seconds for a search. So I would like to ask if You can help me getting faster results or what kind of solution You recommend...
This is the query:
SET @duration="00:10:00";
SET @hourly_start_times="0,15,30,45";
SET @consultant_id=1;
SET @room_id=2;
SET @openingtime_id=1234;
SET @running_date="2017-11-07";
SET @start_times_mandatory=FALSE;
SET @simultaneous_appointments=1;
SELECT timeslots.timeslot FROM timeslots
INNER JOIN openingtimes_timeslots ON openingtimes_timeslots.openingtime_id=@openingtime_id AND openingtimes_timeslots.timeslot_id=timeslots.id
INNER JOIN openingtimes
ON openingtimes.id = openingtimes_timeslots.openingtime_id
INNER JOIN consultants
ON consultants.id=openingtimes.consultant_id
WHERE consultants.id=@consultant_id
AND IF(@start_times_mandatory=TRUE, FIND_IN_SET( MINUTE(timeslots.timeslot), @hourly_start_times), TRUE)
AND openingtimes.room_id=@room_id
AND timeslots.timeslot BETWEEN openingtimes.time_start AND openingtimes.time_end
AND ADDTIME(timeslot, @running_date) BETWEEN openingtimes.time_start AND openingtimes.time_end
AND @running_date BETWEEN openingtimes.date_start AND openingtimes.date_end
AND IF(repeattype="d", MOD(DATEDIFF(@running_date, openingtimes.date_start),intervall)=0, @running_date=openingtimes.date_start)
AND NOT EXISTS ( SELECT exception_date FROM openingtimes_exceptions WHERE exception_date=@running_date AND openingtime_id=openingtimes.id )
AND NOT EXISTS (
SELECT id FROM blockingtimes
WHERE blockingtimes.consultant_id=consultants.id
AND (
concat(date(@running_date)," ",timeslots.timeslot) BETWEEN blockingtimes.blockingtime_start AND blockingtimes.blockingtime_end - INTERVAL 1 SECOND
OR
concat(date(@running_date)," ", ADDTIME(timeslot, @duration) ) BETWEEN blockingtimes.blockingtime_start + INTERVAL 1 SECOND AND blockingtimes.blockingtime_end
)
)
AND ( SELECT COUNT(id) FROM appointments
WHERE DATE(appointments.appointment_start)=@running_date
AND appointments.room_id=@room_id
AND appointments.consultant_id=@consultant_id
AND (
(appointments.appointment_start + INTERVAL 1 SECOND) BETWEEN concat(date(@running_date)," ",timeslots.timeslot) AND (concat(date(@running_date)," ",ADDTIME(timeslots.timeslot, @duration)))
OR
(appointments.appointment_end - INTERVAL 1 SECOND) BETWEEN concat(date(@running_date)," ",timeslots.timeslot) AND (concat(date(@running_date)," ",ADDTIME(timeslots.timeslot, @duration)))
OR (
appointments.appointment_start < concat(date(@running_date)," ",timeslots.timeslot) AND appointments.appointment_end > (concat(date(@running_date)," ",ADDTIME(timeslots.timeslot, @duration)) - INTERVAL 1 SECOND)
)
)
) < @simultaneous_appointments;
The openingtimes can be some kind of recurring events, which can have an intervall of x days and are calculated with modulo.
The records for openingtimes_timeslots are created when an openingtime is created. I tried this as a replacement for joining the timeslots table directly with the openingtimes table and only take the slots fitting into the time window of the openingtime. Mainly because then the join is based on a primary key, but I'm not sure if this will be better in any case.
The hourly starting times can be mandatory or not.
The duration is the desired appointment duration and may take up to an hour, but the openingtimes can't get close to midnight.
This is the EXPLAIN result:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY openingtimes const PRIMARY,time_start,time_end,date_start,date_end,room_id,consultant_id PRIMARY 4 const 1 Using temporary; Using filesort
1 PRIMARY consultants const PRIMARY PRIMARY 4 const 1 Using index
1 PRIMARY openingtimes_timeslots ref openingtime_id,timeslot_id openingtime_id 4 const 55
1 PRIMARY timeslots eq_ref PRIMARY,timeslot PRIMARY 4 calendar.openingtimes_timeslots.timeslot_id 1 Using where
4 DEPENDENT SUBQUERY appointments ref appointment_start,appointment_end,consultant_id,consultant_room_appointment consultant_room_appointment 8 const,const 1488 Using index condition; Using where
3 DEPENDENT SUBQUERY blockingtimes ref consultant_id,blockingtime_start,blockingtime_end consultant_id 4 const 23 Using where
2 MATERIALIZED openingtimes_exceptions ref openingtime_id,exception_date exception_date 3 const 2
This is the table structure:
CREATE TABLE `appointments` (
`id` int(11) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`appointment_start` datetime NOT NULL,
`appointment_end` datetime NOT NULL,
`consultant_id` int(11) NOT NULL,
`appointmenttype_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`status_id` int(11) NOT NULL DEFAULT '1',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `blockingtimes` (
`id` int(11) NOT NULL,
`consultant_id` int(11) NOT NULL,
`blockingtime_start` datetime NOT NULL,
`blockingtime_end` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `consultants` (
`id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`surename` text NOT NULL,
`lastname` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `openingtimes` (
`id` int(11) NOT NULL,
`consultant_id` int(11) NOT NULL,
`repeattype` text NOT NULL,
`intervall` int(11) NOT NULL,
`date_start` date NOT NULL,
`date_end` date NOT NULL,
`time_start` time NOT NULL,
`time_end` time NOT NULL,
`room_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `openingtimes_exceptions` (
`id` int(11) NOT NULL,
`openingtime_id` int(11) NOT NULL,
`exception_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `openingtimes_timeslots` (
`id` int(11) NOT NULL,
`openingtime_id` int(11) NOT NULL,
`timeslot_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `rooms` (
`id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`label` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `timeslots` (
`id` int(11) NOT NULL,
`timeslot` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `appointments`
ADD PRIMARY KEY (`id`),
ADD KEY `appointment_start` (`appointment_start`),
ADD KEY `appointment_end` (`appointment_end`),
ADD KEY `consultant_id` (`consultant_id`),
ADD KEY `consultant_room_appointment` (`consultant_id`,`room_id`,`appointment_start`) USING BTREE;
ALTER TABLE `appointmenttypes`
ADD PRIMARY KEY (`id`);
ALTER TABLE `appointmenttypes_starttimes`
ADD PRIMARY KEY (`id`);
ALTER TABLE `blockingtimes`
ADD PRIMARY KEY (`id`),
ADD KEY `consultant_id` (`consultant_id`),
ADD KEY `blockingtime_start` (`blockingtime_start`) USING BTREE,
ADD KEY `blockingtime_end` (`blockingtime_end`) USING BTREE;
ALTER TABLE `consultants`
ADD PRIMARY KEY (`id`);
ALTER TABLE `openingtimes`
ADD PRIMARY KEY (`id`),
ADD KEY `time_start` (`time_start`) USING BTREE,
ADD KEY `time_end` (`time_end`) USING BTREE,
ADD KEY `date_start` (`date_start`) USING BTREE,
ADD KEY `date_end` (`date_end`) USING BTREE,
ADD KEY `room_id` (`room_id`) USING BTREE,
ADD KEY `consultant_id` (`consultant_id`);
ALTER TABLE `openingtimes_exceptions`
ADD PRIMARY KEY (`id`),
ADD KEY `openingtime_id` (`openingtime_id`) USING BTREE,
ADD KEY `exception_date` (`exception_date`) USING BTREE;
ALTER TABLE `openingtimes_timeslots`
ADD PRIMARY KEY (`id`),
ADD KEY `openingtime_id` (`openingtime_id`) USING BTREE,
ADD KEY `timeslot_id` (`timeslot_id`) USING BTREE;
ALTER TABLE `rooms`
ADD PRIMARY KEY (`id`);
ALTER TABLE `timeslots`
ADD PRIMARY KEY (`id`),
ADD KEY `timeslot` (`timeslot`) USING BTREE;
ALTER TABLE `appointments`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23000;
ALTER TABLE `appointmenttypes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=250;
ALTER TABLE `appointmenttypes_starttimes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=650;
ALTER TABLE `blockingtimes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=350;
ALTER TABLE `consultants`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=250;
ALTER TABLE `openingtimes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2200;
ALTER TABLE `openingtimes_exceptions`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=500;
ALTER TABLE `openingtimes_timeslots`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41000;
ALTER TABLE `rooms`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=50;
ALTER TABLE `timeslots`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=500;
As you can see here, I have not much data in the tables. Nevertheless the execution takes too long for mor than a few users.
I wonder if it is possible to decrease the execution time significantly, because it would not help me much getting e.g. 50% faster execution time.
I also considered about just fetching the existing appointments instead of creating the missing ones, and calculate the free slots in PHP.
Thank you for reading and best regards...
Bingo