I have a Credit/Debit table (5 millions records). The application must provide an UI View to search data providing N criteria:
- Type of date (required) e.g.. Creation date or due-date
- Date range (required)
- Status (paid, non-paid or both)
- Document number
- Customer ID
- Type of payment (credit card, money etc)
The user must provide the first two criteria, but the other may be or not be provided. So I have many combinations.
I think I will have to create many indexes such as:
- ix_search_customerID_dueDate (customerID, dueDate)
- ix_search_customerID_creationDate (customerID, creationDate)
There's uncountable combinations. I use C# and Entity Framework to generate queries based on which values was provided, that's easy to do, but I don't know how to create indexes to cover all possibilities. Is it possible or should I change the UI logic?
I read this answer so based on this I believe that if I create an index (creationDate, status, document, customerID, typeOfPayment) and the user only provides for example Customer ID the index won't work.