We're updating the issue view to help you get more done. 

Free Appointment Slot query uses wrong database index

Description

The find free appointment slot query generates SQL of the form:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT ap.entity_id AS scheduleId, a.activity_end_time AS startTime, min(b.activity_start_time) AS endTime FROM acts a INNER JOIN participations ap ON a.act_id = ap.act_id AND (ap.arch_short_name = 'participation.schedule' AND ap.entity_id = 101505), acts b INNER JOIN participations bp ON b.act_id = bp.act_id AND (bp.arch_short_name = 'participation.schedule' AND bp.entity_id = 101505) WHERE a.arch_short_name = 'act.customerAppointment' AND b.arch_short_name = 'act.customerAppointment' AND (a.activity_start_time BETWEEN '2015-10-01 00:00:00' AND '2015-11-02 00:00:00' OR a.activity_end_time BETWEEN '2015-10-01 00:00:00' AND '2015-11-02 00:00:00') AND (b.activity_start_time BETWEEN '2015-10-01 00:00:00' AND '2015-11-02 00:00:00' OR b.activity_end_time BETWEEN '2015-10-01 00:00:00' AND '2015-11-02 00:00:00') AND b.activity_end_time > a.activity_end_time GROUP BY a.activity_end_time, ap.entity_id HAVING a.activity_end_time < min(b.activity_start_time) ORDER BY a.activity_end_time, ap.entity_id LIMIT 25

This uses the participation_arch_short_name_idx which is not particularly useful in databases with large numbers of appointments.
The query should be changed to force selection of the participation_act_entity_start_time_idx which will greatly reduce the no. of participations needed to be examined.

Environment

None

Status

Assignee

Tim Anderson

Reporter

Tim Anderson

Labels

None

Components

Fix versions

Affects versions

1.8

Priority

Major