CODEWITHSUNDEEP
mongodbperformance
Eduardo Klein
Eduardo Klein

Reputation: 13

Slow MongoDB query on top of index

I have a collection in MongoDB with 1,7 million documents, with 6kb of size in average each document. The database is in a reasonable machine, with 6Gb of RAM and 4 cores

This collection has an index to support my queries. This index has the attributes bellow (String, String and Date) and is only 15 mb in size. (Verified in MongoDB Compass)

{
    "Unit" : 1.0,
    "VendorCode" : 1.0,
    "EmissionDate" : 1.0
}

However, when I run the following query on the collection, it takes a long time to return.

db.Collection.find({
        'Unit': '016406',
        'EmissionDate': {$gte: new ISODate('2018-08-01')}
    }, {
        'Unit': 1,
        'VendorCode': 1,
        'EmissionDate': 1
    })

In my experience with SQL databases, such a query on top of an index of this size would return instantly. However, when I run it on MongoDB, through the shell in the machine or Robo3T, it takes more than 10 minutes to complete!

My feeling is that, when the query finds the documents, for some reason Mongo is retrieving it from storage. But that is just a guess.

Am I forgetting to take into account some basic best practices with MongoDB? Which ideas could you give me to investigate this problem?

Upvotes: 1

Views: 344

Answers (1)

Mạnh Quyết Nguyễn
Mạnh Quyết Nguyễn

Reputation: 18235

Your index does not cover your criteria.

'Unit': '016406',
'EmissionDate': {$gte: new ISODate('2018-08-01')}

You want another index:

{
    "Unit" : 1.0,
    "EmissionDate" : 1.0
}

Your existing index only support the follwing criteria:

{Unit}
{Unit, VendorCode}
{Unit, VendorCode, EmissionDate}

Read more about the prefix in compound index here

Bear in mind that mongodb will use at most one index or an index intersection to cover your query + sort (if any).

Upvotes: 5

Related Questions