Sql single index or multiple index

Question

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?

Answer 1

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.

Answer 2

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.