Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.