CODEWITHSUNDEEP
mongodbdatabasenosql
joe
joe

Reputation: 1911

MongoDB query not using the desired index

I am using the .Net SDK I have a document with the following structure:

public class ChallengeComment
{
    public long Id { get; set; }
    public long ChallengeId { get; set; }
    public long OwnerId { get; set; }
    public DateTime ChallengeCreatedDateTime { get; set; }
    public DateTime CreatedDateTime { get; set; }
    public string Content { get; set; }
}

I have indexed using:

IMongoIndexKeys keys = new IndexKeysDocument {{ "OwnerID", 1 }, { "CreatedDateTime", 1 }, { "ChallengeCreatedDateTime", 1 } };
      IMongoIndexOptions options = IndexOptions.SetUnique(false);
_mongoDatabase.GetCollection("ChallengeComment").EnsureIndex(keys,options);

And the index has been successfully built

And I am performing the following query

var query =
          (from item in collection.AsQueryable<ChallengeComment>()
           where item.OwnerId == 162399
          orderby item.CreatedDateTime, item.ChallengeCreatedDateTime
          select item).Take(10);

however a full table scan is being performed. I know that order matters in the index. I believe this is correct, though I may be mistaken.

Any ideas on what I am doing wrong?

Upvotes: 0

Views: 97

Answers (2)

alvas
alvas

Reputation: 122082

It should have been ... where item.OwnerID = 162399 instead of ... where item.OwnerId in:

Typo Version

var query =(from item in collection.AsQueryable<ChallengeComment>()where item.OwnerId == 162399 orderby item.CreatedDateTime, item.ChallengeCreatedDateTime select item).Take(10);

Corrected Version

var query =
          (from item in collection.AsQueryable<ChallengeComment>()
           where item.OwnerID == 162399
          orderby item.CreatedDateTime, item.ChallengeCreatedDateTime
          select item).Take(10);

Upvotes: 0

joe
joe

Reputation: 1911

OMG think I am about to shoot myself...

In the index I specified one of the keys as OwnerID However in the query I used OwnerId Lower case 'd' Therefore it didn't use the correct index.

I won't make that mistake again!!!

Upvotes: 1

Related Questions