MongoDB indexing - to improve search/query performance

Question

I have been reading about indexing in mongoDB to improve query performance. I have found many useful resources online.

From the mongoDB docs here

Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e. scan every document in a collection, to select those documents that match the query statement. If an appropriate index exists for a query, MongoDB can use the index to limit the number of documents it must inspect

I understand the above completely.I found another resource which was amazingly helpful here

Here they try to find from_user "paasdude" without an index.

db.tweets.find({'from_user':'paasdude'}).explain();
{
"cursor" : "BasicCursor",
"nscanned" : 51748,
"nscannedObjects" : 51748,
"n" : 35,
"millis" : 40,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
   }
 }

Here they add an index to the "from-user" field, from my understanding the value "1" means sort it in ascending order.

db.tweets.ensureIndex({'from_user' : 1});

Here they try to find the from_user "paasdude" with an index.

db.tweets.find({'from_user':'paasdude'}).explain();
{
  "cursor" : "BtreeCursor from_user_1",
  "nscanned" : 35,
  "nscannedObjects" : 35,
  "n" : 35,
  "millis" : 3,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "isMultiKey" : false,
  "indexOnly" : false,
  "indexBounds" : {
    "from_user" : [
        [
            "paasdude",
            "paasdude"
        ]
    ]
  }
}

It is clear that after adding the index query time went from 40 milliseconds to 3.

My Questions:

Although I understand the concept and need for indexing I don't understand how it actually worked.

By giving the "from_user" field the an index of 1 did it sort all the from_user fields in ascending order?
Does indexing only work in ascending (1) or descending (-1)?
Why by simply adding an index of 1 to the from_user field drop the query time down from 40 to 3 milliseconds?
When should indexes be used?

NOTE: I apologise if this question is off-topic for stack overflow. This is a more conceptual question and I wasn't sure where else to ask it. If you know a better place to ask this question. Please let me know and I will move it.

Alex Alex · Accepted Answer · 2015-10-22T10:21:28

You already answered part of your own question. Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e. scan every document in a collection, to select those documents that match the query statement. If an appropriate index exists for a query, MongoDB can use the index to limit the number of documents it must inspect. To give a concrete example, you probably used an index to access the mongoDB documentation rather than reading the documentation from start to finish and that must have saved you quite a bit of time.

Regarding the sort order, in your case the sort order doesn't make much of a difference. The sort order is becomes more relevant if a query returns a large number of records and the output is sorted by index fields. In that scenario the query execution will be faster if the index order matches the sorting order.

It is a bit of black art when you need to create an index, however, as a rule of thumb you should add an index on the field if you are filtering on it frequently and your query times are (getting) slow. As indexes require storage space and need be kept up to date when new records are inserted it is generally good practice to limit the number of indices.

MongoDB indexing - to improve search/query performance

2 Answers