Does order of indexing (ascending vs descending) matter?
Some said for compound indexes then ordering matter. Some says ordering doesn't matter.
Which one is right? And why?
I mean if I look up phone, I don't see how whether the phone is sorted a to z or z to a matter at all. How would that change for compound indexes?
Note: What i mean is ordering in descending or ascending sense. I am well aware that ordering of the column matter.
Note we have 2 answers by high point members. One say that descending or ascending doesn't matter even for compond index. Another said it matters. So yes there is controversy here. Where can I (or we) dig more.
Ordering in the general case does matter. The question is: What ordering are you referring to and does it matter in your case?
Descending vs Ascending. Matters only in very special cases where you actually want the result sorted in a special order. Say you want your result ordered like this:
a asc, b desc, c asc
but your index is
a asc, b asc, c asc
The database has to do an additional sort. The same applies when you want to access the first n elements according to some order. This is more important for composed index, because a change of direction of a single column results in a completely different total ordering, while the ordering of a single column in a single column index just reverses the ordering.
Ordering of the columns so
a, b, c
b, a, c
If you filter for all columns it doesn't make much of a difference, but if you filter only for a, the first index will be more useful than the second.
Whenever you have two options for solving a problem in a programming language and somebody tells you they don't make a difference, ask why the two options exists. If 'somebody' can't answer that, I wouldn't trust his advice on the two being equivalent.
I think the confusion is based on two different meanings of "ordering".
- Whether the indexed items are sorted in ascending or descending order.
- In a compound index, which column is used first to sort the items
The first one, as you mentioned, does not matter. The second does matter, though.