4

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.

user4951
  • 32,206
  • 53
  • 172
  • 282
  • If it's something multidimensional, then it does matter - for example, `array[1][2]` means the 2nd row and 3rd column, while `array[2][1]` is the opposite. –  Sep 09 '12 at 09:01
  • I am aware of that. I am asking about the descending or ascending aspect of it. – user4951 Sep 24 '12 at 15:10

4 Answers4

7

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • Sorry I mean ordering of ascending descending. How come it matters for multiple index. – user4951 Sep 09 '12 at 11:37
  • Does the order of the index column's sort matter for maintenance at all? Like if you define a column in the index to be sorted DESC but all inserts into the table have incrementing values for that index, would a lot of data have to move in order to get that new row indexed at the "top"? – JeremyWeir Jun 20 '17 at 22:27
  • Looks like it does matter for fragmentation... https://stackoverflow.com/a/9382500/45767 – JeremyWeir Jun 20 '17 at 22:30
4

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.

McGarnagle
  • 101,349
  • 31
  • 229
  • 260
1

Let's say you have two fields, name and surname. A compound index on (name, surname) is different from one on (surname, name). Searches will start on the first column and then the second. So if you're searching using surname, a compound index on (name, surname) will be slower than one on (surname, name).

Gigi
  • 28,163
  • 29
  • 106
  • 188
0

For example, create an ascending index on the field a for a collection records:

db.records.createIndex( { a: 1 } )

This index can support an ascending sort on a and also descending sort on a by traversing the index in reverse order:

db.records.find().sort( { a: 1 } )
db.records.find().sort( { a: -1 } )

You are right about phones because a result of searching is sort by default by $natural — https://docs.mongodb.com/manual/reference/method/cursor.sort/#return-in-natural-order

For composite indixes, ordering matters. For example, an index key pattern { a: 1, b: 1 } can support a sort on { a: 1, b: 1 } but not on { b: 1, a: 1 }.

For a query to use a compound index for a sort, the specified sort direction for all keys in the cursor.sort() document must match the index key pattern or match the inverse of the index key pattern. For example, an index key pattern { a: 1, b: -1 } can support a sort on { a: 1, b: -1 } and { a: -1, b: 1 } but not on { a: -1, b: -1 } or {a: 1, b: 1}.

Sourse: https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/

A''
  • 93
  • 2
  • 9