I have a problem with several queries joining to a certain table being very slow.
The table that causes the slow down contains shift data for workers. Certain events happen during shifts so when retrieving the events I need to know whih shifts are involved.
The tables are joined as follows
The related part of the where clause of the query is as follows
AND shift_table.shift_type = event_table.shift_type
AND shift_table.shift_code = event_table.shift_code
AND ((shift_table.start_time >= event_table.start_time
AND shift_table.start_time <= event_table.end_time)
OR (shift_table.start_time <= event_table.start_time
AND shift_table.end_time >= event_table.start_time))
In addition the user specifies a date range so the following clauses will be added:
--Events started in middle of date range
(event_table.start_time >= user_from_date
AND event_table.start_time <= user_to_date)
--Events started before date range
OR (event_table.start_time <= user_from_date
AND event_table.end_time >= user_from_date)
The shift_table will be holding several rows for each day of the year. The event table will grow and hold thousands of events.
Is there an appropriate index or query re-write to improve performance? I have tried adding indexes on the shift ables shift type and shift code columns. I think the problem is the comparison of data ranges and the volume of data involved in the join.
EDIT: I tried an index for each table on its start_time and end_time on it's own i.e. two indexes. This degraded performance considerably.
I should have stated that my pk for the shift table was already what you suggested.
The index suggested for the event table did help , big thanks. I had previously tried two seperate indexes for each date column, but your way seemed to take a bit of time off. I'm not sure if it's enough, the real problem is on our client site, where they are getting crashes from this.
I had a quick look into gathering statistics and that might well be a big help. I might try running this over night and see how big the improvements are.