Forum Indexes

Owned by Christopher Lee (Deactivated)
Last updated: Oct 29, 2015
11 min read

DEVOPS-3170 - Getting issue details... STATUS

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
	}
]

DEVOPS-3157 - Getting issue details... STATUS

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

Proposed "unanswered" indexes: 

DEVOPS-3156 - Getting issue details... STATUS

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
}