Mongodb – The sort column must be the last column used in the index – MongoDB Indexing Advice

indexmongodbsorting

MongoDB docs advices that:

The sort column must be the last column used in the index

Here is the example:

db.foo.ensureIndex({a: 1, b: 1, c: 1})

Good:

find(a=1).sort(a)
find(a=1).sort(b)
find(a=1, b=2).sort(c)

Bad:

find(a=1).sort(c)
even though c is the last column used in the index, a is that last column used, so you can only sort on a or b.

can someone explain this. Why find(a=1).sort(c) considered as Bad?

Best Answer

Think of the index like the business section of the phone book.

There are multiple levels of sorts:

  • a would be category, like "Doctors"
  • b would be last name, like "Smith"
  • c would be first name, like "John"

The listings (rows) are sorted in order of Category -> Last Name -> First name

Using this model, how can you find doctors named John without knowing last names? The answer is, you can't, without looking at every single record (which means not using the index).

In your working examples, you use all the fields left to right without skipping any levels.

In your non-working example, you are looking for the last field and the first without knowing anything in the middle. You can't get to the last level without knowing what's in between.

Related Question