Search code examples
performancemongodbindexingcollectionsmongo-collection

Mongo Query Performance

playing with MongoChef i discovered that the queries are getting slower with the time and with the amount of documents in a collection. EX: I Started an app in November last year and from that time i'm saving data into mongo DB in the same collection without expiration date.

Now i execute this query:

{ "mongoTime": { $gte: "2015-09-01T15:46:51+0200" }, $and: [ { "mongoTime": { $lt: "2015-11-01T15:46:51+0200" } } ] }

It's retrieving 239 691 Documents in 0.178 sec for 2 months time period.

But:

{ "mongoTime": { $gte: "2016-01-27T00:00:00+0000" }, $and: [ { "mongoTime": { $lt: "2016-01-29T11:38:42+0000" } } ] }

Is retrieving 199 909 Documents in 6.391 sec for 3 days time period.

Differences: the first query gets all documents that were the first inserted into Mongo, the second one gets all document from the last 3 days untill now.

Of course the Collection already has some millions of documents, i would like to read some advices about how to deal with this problem. I thought about use indexes: somethig like:

db.clicks.createIndex( { "mongoTime": 1 } )

OR

db.clicks.createIndex( { "mongoTime": -1 } )

but update the indexes for this Collection is gonna take a while and it's better to ask before making errors, and indexes could afect the write performance as well.

thanks in advance.

After reading the correct answer and aplying the index:

{ "mongoTime": { $gte: "2016-01-27T00:00:00+0000" }, $and: [ { "mongoTime": { $lt: "2016-01-29T11:38:42+0000" } } ] }

Is retrieving 199 909 Documents in 0.194 sec for 3 days time period.

Big improvement. Thanks...

Solution 
  • db.clicks.createIndex( { "mongoTime": 1 } )

    Yes, this index will dramatically increase the performance of the queries you provided. MongoDB will be able to check your queries' filtering conditions much more effectively—instead of going through each document in the collection, it will go through a B-tree index, thus greatly reducing the number of nodes it has to visit.

    By the way, you're not using $and correctly. Here's the proper way:

    { $and: [
    { "mongoTime": { $gte: "2016-01-27T00:00:00+0000" } },
    { "mongoTime": { $lt: "2016-01-29T11:38:42+0000" } }
] }