indexing - MongoDB: What index should I use? -
i got highscore mongodb table contains documents such
{username:"bob",score:10,category:"mostlikes"} {username:"john",score:32,category:"mostlikes"} {username:"bob",score:2,category:"leastdeaths"}
the goal fetch top 100 (sorted) of specific category.
important: highscore categories ascending (lower better ex: leastdeaths) , others descending (bigger better ex: mostlikes). means depending on category, want either 100 biggest scores or 100 lowest scores.
there 2 main queries in application:
db.highscore.find({category:category}, {}).limit(100).sort({ score: 1 /*or -1*/ });
db.highscore.find({username:username});
what index recommend?
would keeping ascending category , descending categories in different tables result in better performance?
note: not want have 1 table per category.
i did test on local sample datasets , think best option create index on "category_1_score_1_username_1"
creating index on following fields gives covered query , documents returned index directly.
find below 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 can modify query make return 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 can see output no of documents scanned 0 while records fetched directly index. choosing index best bet first query.
for second query, should simple create index on username field , should solve second query you.
hth.
Comments
Post a Comment