CODEWITHSUNDEEP
mongodbdatabasenosql
nikosdi
nikosdi

Reputation: 2158

MongoDb slow queries with Indexes

We have a MongoDB NoSQL Database that holds some data. Currently the DB contains 10M rows. The default _id field is used as a primary key. Our collection has three variables:

We would like to have an query combining the Timestamp and the variable1. We have an index on the Timestamp and Variable1. In addition, even if this is not correct for range queries we have a compound index on (Timestamp,Variable1).

No when we have queries like the following the performance is very bad(~ 1 minute execution time).

Example Query:

db.getCollection('XXX').find({$and:[
{timestamp:{$lte:1424195749000}},
{timestamp:{$gte:1424195649000}},
{Variable1:1}
]})

A query using only the Variable1 field run about on (100ms).

getIndexes():

{
    "0" : {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "XXXXXX_DB.XXXData"
    },
    "1" : {
        "v" : 1,
        "key" : {
            "timestamp" : 1.0000000000000000
        },
        "name" : "timestamp_1",
        "ns" : "XXXXXX_DB.XXXData"
    },
    "2" : {
        "v" : 1,
        "key" : {
            "timestamp" : -1.0000000000000000
        },
        "name" : "timestamp_-1",
        "ns" : "XXXXXX_DB.XXXData"
    },
    "3" : {
        "v" : 1,
        "key" : {
            "variable1" : 1.0000000000000000
        },
        "name" : "variable1_1",
        "ns" : "XXXXXX_DB.XXXData"
    },
    "4" : {
        "v" : 1,
        "key" : {
            "timestamp" : 1.0000000000000000,
            "variable1" : 1.0000000000000000
        },
        "name" : "timestamp_1_variable1_1",
        "ns" : "XXXXXX_DB.XXXData"
    }
}

Upvotes: 0

Views: 1020

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

You need an index on { Variable1: 1, timestamp: 1 } to speed-up that query (with an upper-case V -- you use "Variable1" in the query but your index seems to be on "variable1")

Given your query:

db.getCollection('XXX').find({$and:[
  {timestamp:{$lte:1424195749000}},
  {timestamp:{$gte:1424195649000}},
  {Variable1:1}
]})

Here, the optimizer will see that you have an equality on Variable1. So this field is the "most limiting". So the optimizer will choose an index having it as a prefix. { Variable: 1} shouldn't be too bad. But { Variable: 1, timestamp: 1} would be better.

Please note that you have redundant indexes:

  • {timestamp:-1} do not add much to {timestamp:1}
  • {Variable1: 1} is useless if you have {Variable1: 1, timestamp: 1}
    (as the former is a prefix of the later)
  • {timestamp: 1} is useless if you have {timestamp: 1, Variable1: 1}
    (as the former is a prefix of the later)

Upvotes: 3

Related Questions