MySQL Forums
Forum List  »  Performance

Re: Find free appointments faster
Posted by: Bingo Bongo
Date: November 08, 2017 10:43PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2958
November 08, 2017 12:32AM
531
November 08, 2017 12:48AM
Re: Find free appointments faster
533
November 08, 2017 10:43PM
545
November 08, 2017 11:31PM
471
November 09, 2017 09:27AM
488
November 14, 2017 10:19AM
538
November 08, 2017 11:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.