CODEWITHSUNDEEP
mongodbindexingdatabase-designmongodb-indexesdatabase
mikegreiling
mikegreiling

Reputation: 1161

MongoDB Indexes Based on $in?

I have a query that looks through log entries which are tagged by priority.

db.logs.find({
    environment: "production",
    priority: {
        $in: ["debug", "info"]
    }
}).sort({
    timestamp: -1
})

This collection is now over 3GB and these queries are taking upwards of 45 seconds to return.

Queries like the following, still return in under a second:

db.logs.find({
    environment: "production",
    priority: "info"
}).sort({
    timestamp: -1
})

It appears that my indexes aren't doing anything to help. Here's what I've tried:

{ "_id" : 1}
{ "timestamp" : -1}
{ "priority" : 1 , "timestamp" : -1}
{ "environment" : 1 , "timestamp" : -1}
{ "environment" : 1 , "priority" : 1 , "timestamp" : -1}

None of these appear to be helping me. Is there some way to create indexes based around groupings? (i.e. an index for all messages where priority: { $in: ["foo", "bar", "bin"] })

Upvotes: 1

Views: 41

Answers (1)

Explosion Pills
Explosion Pills

Reputation: 191789

This excellent blog post explains your exact scenario. Essentially the indexing is sorting first independently and then using your index. To use the range query ($in) you should do the indexing in opposite order: {timestamp: -1, priority: 1}.

Also use .explain to see what your query is doing. With scanAndOrder: true it has to do a full scan of the collection and attempt to sort in memory which is going to take a long time.

Upvotes: 1

Related Questions