How do I optimize my MongoDB Database?

Question

The problem:

I have two collections in my database:

// UnifiedPost
public class UnifiedPost
{
    public UnifiedPost() { Id = ObjectId.GenerateNewId(); }

    [BsonId]
    public ObjectId Id { get; set; }

    public int OriginalId { get; set; }
    public string Thumbnail { get; set; }
    public string Preview { get; set; }
    [AlsoNotifyFor("IsVideo")]
    public string FullSized { get; set; }
    public char Rating { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }
    public string Dimensions { get { return string.Format("{0}x{1}", Width, Height); } }
    public int Score { get; set; }
    [MongoDB.Bson.Serialization.Attributes.BsonIgnore]
    public string Tags { get; set; }
    public ICollection<ObjectId> TagIds { get; set; }
    public char Site { get; set; }

    public bool IsVideo { get { return FullSized.Contains(".mp4") || FullSized.Contains(".webm"); } }

    public string UniversalId { get; set; } // UNIQUE index (Site + Original id; fe. S3001)
}

// Tag
public class Tag
{
    public Tag() { }

    public ObjectId Id { get; set; }
    public string Name { get; set; }
}

There's around 4 million entries in the Posts collection as of now. Queries such as db.Posts.find(), db.Posts.find( { "_id": ObjectId(xyz) } ) and db.Posts.find( { "UniversalId": "S3001" } ) are returning results in a matter of ms.

On the other hand, queries like .find( { "Site": "S" } ) or.find( { "OriginalId": {$lt: "25" } })` can take even 30s to complete.

What I already have:

I'm using an extension method to add/update a unique key to the UniversalId property:

if (!createIndex)
{
    await postsColl.Indexes.AddOrUpdateAsync(new CreateIndexOptions() { Unique = true }, new IndexKeysDefinitionBuilder<UnifiedPost>().Ascending(o => o.UniversalId));
    createIndex = true;
}

What I'm unsure of:

• I don't know if I should use indexes for any other fields, and what types of indexes I should use, for that matter.
• Site holds a char value and there are ~6 possibilities in the collection as of now. It might or might not increase further. *OriginalId is just an int that represents the post on the original site. This id isn't unique, as there is roughly as many 1s, 2s, 3s (and so on) as there are different Sites in the collection.

What would be the best way to optimize the database so I can query posts based on these properties with a reasonable operation time?

Answer 1

What would be the best way to optimize the database so I can query posts based on these properties with a reasonable operation time?

Add indexes on those fields, of course.