Does order of indexing (ascending vs descending) matter?

Question

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.

Answer 1

Ordering in the general case does matter. The question is: What ordering are you referring to and does it matter in your case?

1. 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.

1. Ordering of the columns so

   a, b, c

vs

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.

Answer 2

I think the confusion is based on two different meanings of "ordering".

1. Whether the indexed items are sorted in ascending or descending order.
2. 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.