Search code examples
c#mongodbmongodb-.net-driverdatabase-performance

MongoDB + C#: Compound Indexes on GUID fields not being chosen/used

After understanding more about how indexes work on MongDB, I decided to improve a query that in some months will be very used in a project that I'm currently working on. Basically the collection has this structure:

{
   _id: UUID("0f8fad5b-d9cb-469f-a165-70867728950e"),
   Title: "ABC",
   BookId: UUID("7c9e6679-7425-40de-944b-e07fc1f90ae7"),
   UserId: UUID("7c9e6579-7425-40de-944b-e07fc1f90az9"),
   ModificationDate: ISODATE(...),
   ...
   ...
}

The query will have this two mainly formats:

The app is filtering only using BookId and UserId

db.userBooks.find({BookId: UUID("7c9e6679-7425-40de-944b-e07fc1f90ae7"), UserId: UUID("7c9e6579-7425-40de-944b-e07fc1f90az9")});

The app is filtering now using BookId, UserId and ModificationDate

db.userBooks.find({BookId: UUID("7c9e6679-7425-40de-944b-e07fc1f90ae7"), UserId: UUID("7c9e6579-7425-40de-944b-e07fc1f90az9"), ModificationDate: {$qt: "2015-08-30: 10:25:00.100}});

For this case, I created an Index like this in my collection:

db.UserBooks.createIndex({BookId: 1, UserId: 1, ModificationDate: -1})

After that, I ran a query using BookId and UserId on Mongo Shell with explain() and for my surprise the newly index was not used! I'm not at work right now, so I cannot post the explainable result here but I can say that it did a COLLSCAN on 20.000 keys and documents!

I tried to look online for answers and couldn't find much stuff. What I did find out is that MongoDB does not work well with GUID types. This raised several questions:

1: MongoDB really does not "like" GUID's?

2: There's no "sense" in ordering GUID types ascending or descending. So, when I create an index on these types should I specify always 1: ascending? Does it matter?

3: When querying on collections that have GUID's on Mongo Shell, they appear as BinaryData(hash). Why's that?

This particular application has a SQL Server Database. We use MongoDB for certain cases when we have mutant data or need very fast access. These GUID types are Id's in the SQL Server Database. Does anyone know how to solve this? Or point me to a direction?

Solution
    1. So MongoDb works with GUIDs and interpret them to UUID data format (so called BinData). For more information see: http://docs.mongodb.org/manual/reference/bson-types/ and http://docs.mongodb.org/manual/reference/method/UUID/
    2. In the practice I never saw situations when order of GUID index matters so by default you can use ascending.
    3. See p.1 as explained in documentation - GUIDs really are BinData format in MongoDb BSON Types system. So unique string or ObjectId are reffered types for usage as primary keys (_id). And as result we achieved during comments conversation - composite (multi-fields) index works well with C# Driver queries.