Existing Indexes, Complete Set
Currently there are 24 indexes.
Current Indexes Oct 24th 2016
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "comments-prod.contents",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"parent_id" : 1
},
"ns" : "comments-prod.contents",
"background" : true,
"name" : "parent_id_1"
},
{
"v" : 1,
"key" : {
"parent_ids" : 1
},
"ns" : "comments-prod.contents",
"name" : "parent_ids_1"
},
{
"v" : 1,
"key" : {
"comment_thread_id" : 1
},
"ns" : "comments-prod.contents",
"background" : true,
"name" : "comment_thread_id_1"
},
{
"v" : 1,
"key" : {
"author_id" : 1
},
"ns" : "comments-prod.contents",
"background" : true,
"name" : "author_id_1"
},
{
"v" : 1,
"key" : {
"tags_array" : 1
},
"ns" : "comments-prod.contents",
"background" : true,
"name" : "tags_array_1"
},
{
"v" : 1,
"key" : {
"last_activity_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "last_activity_at_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"author_id" : 1,
"course_id" : 1
},
"ns" : "comments-prod.contents",
"name" : "author_id_1_course_id_1"
},
{
"v" : 1,
"key" : {
"votes.up" : 1,
"_type" : 1
},
"ns" : "comments-prod.contents",
"name" : "votes.up_1__type_1",
"background" : true
},
{
"v" : 1,
"key" : {
"votes.down" : 1,
"_type" : 1
},
"ns" : "comments-prod.contents",
"name" : "votes.down_1__type_1",
"background" : true
},
{
"v" : 1,
"key" : {
"commentable_id" : 1,
"created_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "commentable_id_1_created_at_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"course_id" : 1,
"_type" : 1,
"created_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "course_id_1__type_1_created_at_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"comment_thread_id" : 1,
"author_id" : 1,
"updated_at" : 1
},
"ns" : "comments-prod.contents",
"name" : "_type_1_comment_thread_id_1_author_id_1_updated_at_1",
"background" : true
},
{
"v" : 1,
"key" : {
"comment_thread_id" : 1,
"sk" : 1
},
"ns" : "comments-prod.contents",
"name" : "comment_thread_id_1_sk_1",
"background" : true,
"sparse" : true
},
{
"v" : 1,
"key" : {
"comment_thread_id" : 1,
"endorsed" : 1
},
"ns" : "comments-prod.contents",
"name" : "comment_thread_id_1_endorsed_1",
"background" : true,
"sparse" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"course_id" : 1,
"pinned" : -1,
"created_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "_type_1_course_id_1_pinned_-1_created_at_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"delete_spam" : 1
},
"ns" : "comments-prod.contents",
"name" : "delete_spam_1",
"background" : true,
"sparse" : true,
"safe" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"course_id" : 1,
"pinned" : -1,
"comment_count" : -1,
"created_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "_type_1_course_id_1_pinned_-1_comment_count_-1_created_at_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"course_id" : 1,
"pinned" : -1,
"votes.point" : -1,
"created_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "_type_1_course_id_1_pinned_-1_votes.point_-1_created_at_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"commentable_id" : 1
},
"ns" : "comments-prod.contents",
"name" : "commentable_id_1",
"sparse" : true,
"background" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"course_id" : 1,
"context" : 1,
"pinned" : -1,
"created_at" : -1
},
"name" : "_type_1_course_id_1_context_1_pinned_-1_created_at_-1",
"ns" : "comments-prod.contents",
"background" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"commentable_id" : 1,
"context" : 1,
"pinned" : -1,
"created_at" : -1
},
"name" : "_type_1_commentable_id_1_context_1_pinned_-1_created_at_-1",
"ns" : "comments-prod.contents",
"background" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"context" : 1
},
"name" : "_type_1_context_1",
"ns" : "comments-prod.contents",
"background" : true
},
{
"v" : 1,
"key" : {
"_type" : -1,
"course_id" : 1,
"context" : 1,
"pinned" : -1,
"last_activity_at" : -1,
"created_at" : -1
},
"name" : "_type_-1_course_id_1_context_1_pinned_-1_last_activity_at_-1_created_at_-1",
"ns" : "comments-prod.contents",
"background" : true
}
] Suggested For Removal: Other compound indexes take priority over "comment_thread_id_1"
Remove comment_thread_id_1?
{
"v" : 1,
"key" : {
"comment_thread_id" : 1
},
"ns" : "comments-prod.contents",
"background" : true,
"name" : "comment_thread_id_1"
},
{
"v" : 1,
"key" : {
"comment_thread_id" : 1,
"sk" : 1
},
"ns" : "comments-prod.contents",
"name" : "comment_thread_id_1_sk_1",
"background" : true,
"sparse" : true
},
{
"v" : 1,
"key" : {
"comment_thread_id" : 1,
"endorsed" : 1
},
"ns" : "comments-prod.contents",
"name" : "comment_thread_id_1_endorsed_1",
"background" : true,
"sparse" : true
}, Other compound indexes take priority over "author_id_1"
Remove author_id_1?
{
"v" : 1,
"key" : {
"author_id" : 1
},
"ns" : "comments-prod.contents",
"background" : true,
"name" : "author_id_1"
},
{
"v" : 1,
"key" : {
"author_id" : 1,
"course_id" : 1
},
"ns" : "comments-prod.contents",
"name" : "author_id_1_course_id_1"
},
{
"v" : 1,
"key" : {
"_type" : 1,
"comment_thread_id" : 1,
"author_id" : 1,
"updated_at" : 1
},
"ns" : "comments-prod.contents",
"name" : "_type_1_comment_thread_id_1_author_id_1_updated_at_1",
"background" : true
}, Other compound indexes take priority over "commentable_id_1"
Remove commentable_id_1?
{
"v" : 1,
"key" : {
"commentable_id" : 1
},
"ns" : "comments-prod.contents",
"name" : "commentable_id_1",
"sparse" : true,
"background" : true
},
{
"v" : 1,
"key" : {
"commentable_id" : 1,
"created_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "commentable_id_1_created_at_-1",
"background" : true
},
{
"v" : 1,
"key" : {
"_type" : 1,
"commentable_id" : 1,
"context" : 1,
"pinned" : -1,
"created_at" : -1
},
"name" : "_type_1_commentable_id_1_context_1_pinned_-1_created_at_-1",
"ns" : "comments-prod.contents",
"background" : true
}, For Further Review: I don't believe this query ever runs by itself.
Remove last_activity_at?
{
"v" : 1,
"key" : {
"last_activity_at" : -1
},
"ns" : "comments-prod.contents",
"name" : "last_activity_at_-1",
"background" : true
}, We currently do not support showing the least votes on the browser nor does it make sense to.
Remove votes.down?
{
"v" : 1,
"key" : {
"votes.down" : 1,
"_type" : 1
},
"ns" : "comments-prod.contents",
"name" : "votes.down_1__type_1",
"background" : true
}, I don't know if this query ever runs by itself but it might exist as a catchall in case an index for most upvotes does not exist? The only combinations in the browser are "most voted" * ("all", "unread", "pinned", "flagged"). Would require more digging. Also, votes.point is the sum of votes.up and vote.down, I don't know what the use case would be for just getting only the most upvoted.
votes.up
{
"v" : 1,
"key" : {
"votes.up" : 1,
"_type" : 1
},
"ns" : "comments-prod.contents",
"name" : "votes.up_1__type_1",
"background" : true
}, When running `db.contents.disctinct("delete_spam")`, there are no entries.
Remove delete_spam?
{
"v" : 1,
"key" : {
"delete_spam" : 1
},
"ns" : "comments-prod.contents",
"name" : "delete_spam_1",
"background" : true,
"sparse" : true,
"safe" : true
}, I'm not sure if this ever gets call or not.
Remove _type&context?
{
"v" : 1,
"key" : {
"_type" : 1,
"context" : 1
},
"name" : "_type_1_context_1",
"ns" : "comments-prod.contents",
"background" : true
}, No index for this query. It searches by commentable_id but this is not sufficient. Suggested to add a more specific index?
https://rpm.newrelic.com/accounts/88178/applications/3973175/transactions#id=5b22436f6e74726f6c6c65722f53696e617472612f53696e617472613a3a4170706c69636174696f6e2f474554206170692f76312f285b5e2f3f235d2b292f74687265616473222c22225d&app_trace_id=94a864-99acc1d0-7bb1-11e5-a7c8-f8bc12425d50
https://openedx.atlassian.net/browse/DEVOPS-3156
endorsed_date db.contents.ensureIndex({"_type" : -1, "course_id" : 1, "pinned" : -1, "endorsed": -1, "last_activity_at" : -1, "created_at": -1}, {"background":true}) Example query for date
db.contents.find({_type:"Comment", course_id:"BerkeleyX/ColWri2.2x/1T2014", endorsed:false}).sort({"pinned":-1, "last_activity_at":-1}).explain()
{
"cursor" : "BtreeCursor _type_1_course_id_1_context_1_pinned_-1_created_at_-1",
"isMultiKey" : false,
"n" : 45600,
"nscannedObjects" : 45612,
"nscanned" : 45612,
"nscannedObjectsAllPlans" : 409789,
"nscannedAllPlans" : 409793,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 7308,
"nChunkSkips" : 0,
"millis" : 71146,
"indexBounds" : {
"_type" : [
[
"Comment",
"Comment"
]
],
"course_id" : [
[
"BerkeleyX/ColWri2.2x/1T2014",
"BerkeleyX/ColWri2.2x/1T2014"
]
],
"context" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
],
"pinned" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"created_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"server" : "c0:27017",
"filterSet" : false
}
endorsed_votes db.contents.ensureIndex({"_type" : -1, "course_id" : 1, "pinned" : -1, "endorsed": -1, "votes.point" : -1, "created_at": -1}, {"background":true}) Example query for votes.point
db.contents.find({_type:"Comment", course_id:"BerkeleyX/ColWri2.2x/1T2014", endorsed:false}).sort({"pinned":-1, "votes.point":-1}).explain()
{
"cursor" : "BtreeCursor _type_1_course_id_1_pinned_-1_votes.point_-1_created_at_-1",
"isMultiKey" : false,
"n" : 45600,
"nscannedObjects" : 45612,
"nscanned" : 45612,
"nscannedObjectsAllPlans" : 46420,
"nscannedAllPlans" : 46428,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 362,
"nChunkSkips" : 0,
"millis" : 181,
"indexBounds" : {
"_type" : [
[
"Comment",
"Comment"
]
],
"course_id" : [
[
"BerkeleyX/ColWri2.2x/1T2014",
"BerkeleyX/ColWri2.2x/1T2014"
]
],
"pinned" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"votes.point" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"created_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"server" : "c0:27017",
"filterSet" : false
}
endorsed_comments db.contents.ensureIndex({"_type" : -1, "course_id" : 1, "pinned" : -1, "endorsed": -1, "comment_count" : -1, "created_at": -1}, {"background":true}) Example query for comment count
db.contents.find({_type:"Comment", course_id:"BerkeleyX/ColWri2.2x/1T2014", endorsed:false}).sort({"pinned":-1, "comment_count":-1}).explain()
{
"cursor" : "BtreeCursor _type_1_course_id_1_pinned_-1_comment_count_-1_created_at_-1",
"isMultiKey" : false,
"n" : 45600,
"nscannedObjects" : 45612,
"nscanned" : 45612,
"nscannedObjectsAllPlans" : 46412,
"nscannedAllPlans" : 46420,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 362,
"nChunkSkips" : 0,
"millis" : 167,
"indexBounds" : {
"_type" : [
[
"Comment",
"Comment"
]
],
"course_id" : [
[
"BerkeleyX/ColWri2.2x/1T2014",
"BerkeleyX/ColWri2.2x/1T2014"
]
],
"pinned" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"comment_count" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
],
"created_at" : [
[
{
"$maxElement" : 1
},
{
"$minElement" : 1
}
]
]
},
"server" : "c0:27017",
"filterSet" : false
}