Slow query about Mongodb with index

Asked

Viewed 295 times

4

I have a Mongodb collection of about 1.7 million documents, averaging 6kb per document. The bank is in a reasonable machine, with 6Gb of RAM and 4 Cpus

This collection has an index on all fields of my query.

The index has the following attributes (String, String and Date) and has the size of 15 Mb. (I checked by Mongodb Compass)

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

However, when I run any query on top of Collection, even if restricted to index attributes, it takes a long time to complete.

Query Example

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

In my experience with SQL, a query that is restricted to an index of this size would return result in fractions of a second. However when I run the query, directly in the shell or with Robo3t, it takes more than 10 minutes!

The impression that gives me is that, even though the data being contained all in the index, when there is match of the document, it searches equal of Storage.

Am I not taking into account some basic precept of Mongodb? What options would indicate me to investigate this problem?

mongodb

  • Did you solve it? Did you identify the problem? Did you use explain to check the query?

  • You can use an Aggregate and clear the return data just so you want. replaceRoot or project . I have consultation that the Aggregate and better

1 answer

0

The order of attributes in the index and query influence the search. You have an index with three levels and are skipping one of them in the query.

Two attempts:

  1. Try creating another index that does not have the Vendorcode field.

  2. Or try to include Vendorcode in the query. Here depends more on your business model being represented, but it would be an alternative.

Browser other questions tagged mongodb

You are not signed in. Login or sign up in order to post.