Which index will be used in this scenario?
Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last in the index, after all the columns that have an equality check.

This allows the optimizer to use an index seek to the first row that matches the conditions and then traverse the index until it finds a row that doesn't match it. All the rows in between are a match, too.

So, the best index for this query would be either (to, from, date) (your model A) or (from, to, date).

The model B index has the date first so it is not the best, although it is still a covering index for the query. If this was used, the query plan would be almost the same. An index seek to find the first row that matches the range condition (date &gt; '2016-02-28') and then traverse the index until it finds a row that doesn't match the date &lt; '2016-04-01'. But all the rows in between do not necessarily match the 2 other conditions so they would have to be checked against these conditions and (possibly many of them) rejected.

So while the plans would be similar, the model A plan would have to only go through the part of the index that has all the needed rows and only them, while the model B plan would go through a (possibly much) larger part of the index.




It would also be best to use a 100% safe format for dates (YYYYMMDD).
And if you want the dates in March, you should use an inclusive-exclusive check:

AND flightdate &gt;= '20160301' AND flightdate &lt; '20160401' 


Guaranteed to work with date and datetime types. Your current query will include also any row that has '2016-02-28' but a time different to '00:00:00' (can you guarantee that there isn't any?) which I assume you don't want. The inclusive-exclusive method will also work in leap years (reminding that 2016 is a leap year so there was a February 29th date as well which your query will return).


Read also these blog posts by Aaron Bertrand:  


What do BETWEEN and the devil have in common?
Bad habits to kick : mis-handling date / range queries