MongoDB: What index should I use?

Question

I got a highscore mongodb table that contains documents such as

{username:"Bob",score:10,category:"mostLikes"} {username:"John",score:32,category:"mostLikes"} {username:"Bob",score:2,category:"leastDeaths"}

The goal is to fetch the top 100 (sorted) of a specific category.

Important: Certain highscore categories are ascending (lower is better ex: leastDeaths) and others are descending (bigger is better ex: mostLikes). This means that depending on the category, I want either the 100 biggest scores or the 100 lowest scores.

There are two main queries in my application:

db.highscore.find({category:category}, {}).limit(100).sort({ score: 1 /*or -1*/ });

db.highscore.find({username:username});

What index would you recommend?

Would keeping ascending category and descending categories in different tables result in better performance?

Note: I do not want to have one table per category.

Answer 1

I did some test on my local with some sample datasets and i think the best option would be to create an index on "category_1_score_1_username_1"

Creating an index on the following fields gives you a covered query and thus the documents are returned from the index directly.

Find below my analysis

> db.usr.find();
{ "_id" : ObjectId("57bd20630744bd376277a795"), "username" : "Bob", "score" : 10, "category" : "mostLikes" }
{ "_id" : ObjectId("57bd20630744bd376277a796"), "username" : "John", "score" : 32, "category" : "mostLikes" }
{ "_id" : ObjectId("57bd20630744bd376277a797"), "username" : "Bob1", "score" : 2, "category" : "leastDeaths" }
{ "_id" : ObjectId("57bd20630744bd376277a798"), "username" : "John2", "score" : 132, "category" : "mostLikes" }
{ "_id" : ObjectId("57bd20630744bd376277a799"), "username" : "Bob3", "score" : 20, "category" : "leastDeaths" }
{ "_id" : ObjectId("57bd20630744bd376277a79a"), "username" : "John4", "score" : 132, "category" : "mostLikes" }
{ "_id" : ObjectId("57bd20630744bd376277a79b"), "username" : "Bob5", "score" : 22, "category" : "leastDeaths" }
{ "_id" : ObjectId("57bd20630744bd376277a79c"), "username" : "John6", "score" : 322, "category" : "mostLikes" }
{ "_id" : ObjectId("57bd20630744bd376277a79d"), "username" : "Bob7", "score" : 232, "category" : "leastDeaths" }
{ "_id" : ObjectId("57bd20630744bd376277a79e"), "username" : "John8", "score" : 3112, "category" : "mostLikes" }
{ "_id" : ObjectId("57bd20630744bd376277a79f"), "username" : "Bob4", "score" : 222, "category" : "leastDeaths" }
{ "_id" : ObjectId("57bd20630744bd376277a7a0"), "username" : "John22", "score" : 3210, "category" : "mostLikes" }
{ "_id" : ObjectId("57bd20630744bd376277a7a1"), "username" : "Bob33", "score" : 2111, "category" : "leastDeaths" }

Indexes:

> db.usr.getIndexes();
        {
                "v" : 1,
                "key" : {
                        "category" : 1,
                        "score" : 1,
                        "username" : 1
                },
                "name" : "category_1_score_1_username_1",
                "ns" : "test.usr"
        }
]
>

Now you can modify your query slightly to make it return a covered query.

    db.usr.find({"category":"mostLikes"},{"_id":0,"score":-1,"category":1,"username":1}).sort({"score":1}).explain("executionStats");

Output of Execution Stats:

{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.usr",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "category" : {
                                "$eq" : "mostLikes"
                        }
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "score" : -1,
                                "category" : 1,
                                "username" : 1
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "category" : 1,
                                        "score" : 1,
                                        "username" : 1
                                },
                                "indexName" : "category_1_score_1_username_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "category" : [
                                                "[\"mostLikes\", \"mostLikes\"]"
                                        ],
                                        "score" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "username" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 7,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 7,
                "totalDocsExamined" : 0,
                "executionStages" : {
                        "stage" : "PROJECTION",
                        "nReturned" : 7,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 8,
                        "advanced" : 7,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "transformBy" : {
                                "_id" : 0,
                                "score" : -1,
                                "category" : 1,
                                "username" : 1
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 7,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 8,
                                "advanced" : 7,
                                "needTime" : 0,
                                "needYield" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                        "category" : 1,
                                        "score" : 1,
                                        "username" : 1
                                },
                                "indexName" : "category_1_score_1_username_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "category" : [
                                                "[\"mostLikes\", \"mostLikes\"]"
                                        ],
                                        "score" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "username" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                },
                                "keysExamined" : 7,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                        }
                }
        },
        "serverInfo" : {
                "host" : "L4156409",
                "port" : 27017,
                "version" : "3.2.5",
                "gitVersion" : "34e65e5383f7ea1726332cb175b73077ec4a1b02"
        },
        "ok" : 1
}
>

Thus as you can see the output the no of documents scanned is 0 while the records are fetched directly from the index. Thus choosing this index would be your best bet for the first query.

For the second query, that should be simple to create an index on the username field and that should solve the second query for you.

HTH.