1

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?

jpgrassi
  • 5,482
  • 2
  • 36
  • 55
  • 1. Can't say so, but GUIDs are interpreted as binary data (like binary array). 2. No matter. 3. See 1. For keys are preferred usage of ObjectId/String (you can make custom generator for string based on Guid generator). But from my experience Guid keys works well in index. Are you sure that index was created to the moment of query run (don't check "Create in background" to ensure this). – DrAlligieri Aug 30 '15 at 14:10
  • I could change the BookId and UserId to strings, but the _id no. Let's say it is an application already in production and changing the type of the _id will cause several problems. and will require restructuring the applications logic. I'll make this test with the parameters on the query changed to string and see what happens. – jpgrassi Aug 30 '15 at 14:17
  • i try to check format of my queries when query such index (with GUIDs) and provide you additional information. As I say for me "GUIDed" indexes works well. – DrAlligieri Aug 30 '15 at 14:21
  • 1
    as I see C# driver for MongoDb generates for GUIDs in queries representation like this "new BinData(3,"")". And as I see such execution uses my index (contains Guid and Int64 fields). But when I run it with UUID format it works fine with index too. – DrAlligieri Aug 30 '15 at 14:32
  • Humm.. so using the c# driver it does all the "magic" for you? I tried using mongo Shell to query with UUID format but it yells an error.. some kind UUID string in wrong format. Is there a way to run the query in c# and inspect if it used an index? – jpgrassi Aug 30 '15 at 14:44
  • 1
    Yep, seems that MongoDb C# driver support it. http://stackoverflow.com/questions/13254784/is-there-an-explain-query-for-mongodb-linq But I use MongoVUE for query testing – DrAlligieri Sep 01 '15 at 11:45
  • You were right. Calling the query via c# does use the index. To really see it worked I set the ProfilingLevel to 2 and after the query got executed I could see that the Index was really used. Only the amount of returned documents were scanned. The problem was probably because I was executing the query using the Shell and got lost with the UUID's. – jpgrassi Sep 02 '15 at 12:18

1 Answers1

1
  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.
DrAlligieri
  • 211
  • 5
  • 10