Let's say I have the Products table.
On my UI, I allow the user to search by name, description, code The user can only search on criteria.
Should I create an index for each criteria : name, description, code or create ONE single index for all 3?
What will make you choose one versus the other?
Whenever you build an index on multiple columns, say create index .. on T(A, B, C), the index can only be used if the leftmost columns are specified. If you search on column A, the index can be used. If you search on columns A and B then the index can be used. If you search on columns A and B and C then the index can be used. But the index will not be used if you search on column B only, or on column C only or on columns B and C only.
So if you want to search on Product or on Description or on Code you need separate indexes on each. If you want to search for a term in any of the three columns then most likely you need a Full Text search. Same goes for Description, is very unlikely you want a normal index on it, most likely you need a Full Text index on it.
You need an index for each. One index for all three will only benefit searches involving the first criterion in the index, or that in combination with the second, etc.
The answer is: It depends.
The optimizer is what decides, basing on the statistics for the table followed by the index(es) on the columns in the table & the query. Forcing an index doesn't ensure the most efficient/best performing query...
All you can do is test various indexing approaches against the most likely query using the Actual Execution Plan (the lower the subtree cost, the better), and choose what's best to you.
If you constrain searches to use the starts-with string match pattern ( 'search%' ), then you need an index on that column. This index is ordered data that can be seeked into looking for the first match, and read continuously until it hits the last match.
If you have an unconstrained search pattern ( '%search%' ), or allow any combination of the three columns to be seearched, you need a single index for all three. This index is a copy of the columns which may be scanned faster than the original table.
As always, measure measure and measure
SET STATISTICS IO ON
SET STATISTICS TIME ON
The others are correct, you need an index for each column. However, I'd like to add two more things:
A single index (per column) is sufficient regardless of how you order your results (ASCending, DESCending)
For columns like title and description, you may want to consider a full text search instead of the regular sql comparisons and the LIKE filter.
