MySQL Forums
Forum List  »  Performance

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

Sorry, I just realised that I added a wrong column to an index, but this did not made a noticeable difference for the performance.

This is the EXPLAIN of the query with the 3 added count-values with corrected index...

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 	
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
6 	DEPENDENT SUBQUERY 	appointments 	ref 	consultant_id,date_room_consultant_timestart,date_room_consultant_timestart_timeend,date_room_consultant_timeend 	date_room_consultant_timestart_timeend 	11 	const,const,const 	19 	Using where; Using index
5 	DEPENDENT SUBQUERY 	appointments 	ref 	consultant_id,date_room_consultant_timestart,date_room_consultant_timestart_timeend,date_room_consultant_timeend 	date_room_consultant_timeend 	11 	const,const,const 	19 	Using where; Using index
4 	DEPENDENT SUBQUERY 	appointments 	ref 	consultant_id,date_room_consultant_timestart,date_room_consultant_timestart_timeend,date_room_consultant_timeend 	date_room_consultant_timestart 	11 	const,const,const 	19 	Using where; Using index
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 	

Options: ReplyQuote


Subject
Views
Written By
Posted
2958
November 08, 2017 12:32AM
531
November 08, 2017 12:48AM
533
November 08, 2017 10:43PM
545
November 08, 2017 11:31PM
471
November 09, 2017 09:27AM
488
November 14, 2017 10:19AM
Re: Find free appointments faster
537
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.