Hi Øystein Grøvlen,
first I would like to thank you for your assistance and put forward the reason for adding/subtracting a second.
Appointments are traditionally communicated and displayed like for example "from 08:00 to 08:10" for a 10 minute appointment.
When I have 2 appointments...
08:00:00 to 08:10:00
08:20:00 to 08:30:00
...then in the real world we "see" that there is a free slot from 08:10:00 to 08:20:00.
But my query is filtering out timeslots due to overlapping in mathematical terms when there is a previous or a next appointment for the examined timeslot.
So I add a second to a possible start (08:10:01) and subtract a second to a possible end (08:19:59).
Perhaps there is a more elegant solution, but this is just the reason how I solved it to get correct free timeslots.
I made some tests based on your suggestions and first of all I added a dedicated date column to the table and changed the index.
That alone significantly increased the execution speed about 300%, which I really appreciate!
Then I added dedicated columns for starttime and endtime, changed the subquery, the index and eliminated the OR conditions just for index testing purposes...
Index...
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 `date_room_consultant_timestart` (`appointment_date`,`room_id`,`consultant_id`,`appointment_time_start`) USING BTREE;
Subquery...
AND (
( SELECT COUNT(id) FROM appointments
WHERE appointments.appointment_date=@running_date
AND appointments.room_id=@room_id
AND appointments.consultant_id=@consultant_id
AND (appointments.appointment_time_start + INTERVAL 1 SECOND) BETWEEN timeslots.timeslot AND ADDTIME(timeslots.timeslot, @duration)
)
< @simultaneous_appointments )
Of course this subquery did not result in correct free timeslots, but surprisingly this did not increase the query speed very much, just about 30%. In respect of the proposal to get rid of the OR conditions I thought that this query would just be like a single part of the serial added condition values. Just to be sure I also made tests with the whole replacement for the OR conditions...
Index...
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 `date_room_consultant_timestart` (`appointment_date`,`room_id`,`consultant_id`,`appointment_time_start`) USING BTREE,
ADD KEY `date_room_consultant_timeend` (`appointment_date`,`room_id`,`consultant_id`,`appointment_time_start`) USING BTREE,
ADD KEY `date_room_consultant_timestart_timeend` (`appointment_date`,`room_id`,`consultant_id`,`appointment_time_start`,`appointment_time_end`) USING BTREE;
Subquery...
AND (
(
SELECT COUNT(id) FROM appointments
WHERE appointments.appointment_date=@running_date
AND appointments.room_id=@room_id
AND appointments.consultant_id=@consultant_id
AND (appointments.appointment_time_start + INTERVAL 1 SECOND) BETWEEN timeslots.timeslot AND ADDTIME(timeslots.timeslot, @duration)
) +
(
SELECT COUNT(id) FROM appointments
WHERE appointments.appointment_date=@running_date
AND appointments.room_id=@room_id
AND appointments.consultant_id=@consultant_id
AND (appointments.appointment_time_end - INTERVAL 1 SECOND) BETWEEN timeslots.timeslot AND ADDTIME(timeslots.timeslot, @duration)
) +
(
SELECT COUNT(id) FROM appointments
WHERE appointments.appointment_date=@running_date
AND appointments.room_id=@room_id
AND appointments.consultant_id=@consultant_id
AND appointments.appointment_time_start < timeslots.timeslot
AND appointments.appointment_time_end > ADDTIME(timeslots.timeslot, @duration - INTERVAL 1 SECOND)
)
)
< @simultaneous_appointments
...but this was not faster than the query with the OR-conditions either.
Do you have an Idea whats going wrong here?
Many thanks and best regards
Bingo