CODEWITHSUNDEEP
mongodbnosql
user1639326
user1639326

Reputation: 83

mongodb not using indexes

I have a collection with these indexes:

db.colaboradores.getIndexKeys()
[ { "_id" : 1 }, { "nome" : 1 }, { "sobrenome" : 1 } ]

and a query like

db.colaboradores.find({_id: ObjectId("5040e298914224dca3000006")}).explain();

thatworks fine with index

{
    "cursor" : "BtreeCursor _id_",
    "nscanned" : 0,
    "nscannedObjects" : 0,
    "n" : 0,
    "millis" : 0,
}

but when run:

db.colaboradores.find({nome: /^Administrador/}).explain()

mongodb do not use indexes any more:

{
    "cursor" : "BtreeCursor nome_1",
    "nscanned" : 10000,
    "nscannedObjects" : 10000,
    "n" : 10000,
    "millis" : 25,
}

any solutions? Thanks!

Upvotes: 8

Views: 14941

Answers (2)

shelman
shelman

Reputation: 2699

It did use an index - you can tell because the cursor was a BtreeCursor. You have a lot (10000) of documents in your collection where 'nome' equals 'Administrador'.

An explanation of the output:

"cursor" : "Btree_Cursor nome_1" means that the database used an ascending index on "nome" to satisfy the query. If no index were used, the cursor would be "BasicCursor".

"nscanned" : The number of documents that the database had to check ("nscannedObjects" is basically the same thing for this query)

"n" : The number of documents returned. The fact that this is the same as "nscanned" means that the index is efficient - it didn't have to check any documents that didn't match the query.

Upvotes: 6

William Z
William Z

Reputation: 11129

The behaviour you're seeing is expected from MongoDB. This is generally true for any query where you are using a compound index -- one with multiple fields.

The rules of thumb are:

If you have an index on {a:1, b:1, c:1}, then the following queries will be able to use the index efficiently:

find(a)
find(a,b)
find(a,b,c)
find(a).sort(a)
find(a).sort(b)
find(a,b).sort(b)
find(a,b).sort(c)

However, the following queries will not be able to take full advantage of the index:

find(b)
find(c)
find(b,c)
find(b,c).sort(a)

The reason is the way that MongoDB creates compound indexes. The indexes are btrees, and the nodes are present in the btree in sorted order, with the left-most field being the major sort, the next field being the secondary sort, and so on.

If you skip the leading member of the index, then the index traversal will have to skip lots of blocks. If that performance is slow, then the query optimizer will choose to use a full-collection scan rather than use the index.

For more information about MongoDB indexes, see this excellent article here:

Upvotes: 20

Related Questions