What is optimal order for an index on an object?

3
I have a policy that I always retrieve based on 3 attributes: PolicySymbol, PolicyNumber and ModuleNumber. I've created one index on object Policy for which I selected those three attributes. However, I see that I can change the order of the Index attributes. What does this order indicate and what would be the optimal order? Another option would be to create three indexes with each one attribute. But I guess that would not be a proper solution. In which circumstance would I define two indexes on one object? Thanks in advance!
asked
1 answers
3

Indexes optimize search on a (combinations of) specific attribute(s).

I am not sure about this, but i think more unique attributes should be higher in the index least (e.g. your index should first look to BSN and than to surname, since the latter one is not necessarly unique for which reason the database needs to inspect more records)

You need separate indexes if you query on individual attributes, and a combined index if you query on a combination of attributes (e.g: a query with and-ed or or-ed constraints).

I guess a complex query based on multiple attributes, will still take advantage on indexes created for the individual attributes, even when no combined index is available.

Note that each index will make search queries faster, but writes to the database slower, since the indexes needs to be updated.

answered