I have about 1 million records. When searching through the date range, it is surprisingly slow. Then I do the google search and someone suggested to do the index on the "DateTime" column. I am not convinced because I always thought index is for O(1) search - which means equality search; and since my query is a date range search, how can it be possible to improve the performance?
But, out of curiosity, I tried anyway and to my surprise, it does improve the performance significantly.
Here is my table
tblOrder -------- OrderId (Primary Key) CurrencyId ProductId ... ... ... OrderDate DATETIME
I am using linqtosql and the code is:
return (from rec in tblOrders.AsNoTracking() where (rec.CurrencyId == CurrencyId) && (rec.OrderDate >= startDate) && (rec.OrderDate < endDate) select rec).ToList();
I created a composite index on both
OrderDate. The result is a huge improvement. My question is, how is it possible? Why do indexes work on range search (not equal but >= or <=).
Can someone explain to me how does SQL index work in regards to date range search?