I have a query like this:
SELECT * FROM my_first_table a -- > 1 million records INNER JOIN my_second_table b -- > 1 million records on a.id = b.a_id LEFT OUTER JOIN my_third_table c -- > 1 million records on a.id = c.a_id WHERE b.my_column_x IS NOT NULL OR c.my_column_y IS NOT NULL AND b.start_date > isnull(@startdate, '17530101') AND b.end_date < isnull(@enddate, '99991231')
my_column_y are both indexed, however as I'm doing an
OR IS NOT NULL across both of these columns, neither index is being used, and the query is extremely slow.
I'm also doing a date range check.
b.end_date are both
datetime columns, and are indexed. The query accepts inputs where both the start and end dates can be null or a specified datetime.
How can I optimize the query? I'm thinking there must be a way I can do some kind of separate sub-selects for
IS NOT NULL on
my_third_table.my_column_y then combine those sub-selects in order to be able to join in the way I'm trying to do in the original query, so that the indexes on
b.my_column_y are used.