MongoDB : Indexing

Indexing is one of the most important concepts for any database. Without indexes the mongod process would scan the entire collection and the all the documents it contains to obtain the result of the query. Indexes are defined for the collections and properties as well as sub-fields are supported. Briefly, MongoDB supports the following types of indexes :

  • Single Field Indexes : Think about having an index on a column in RDBMS.
  • Compound Indexes: Think about an having an index on multiple columns in RDBMS.
  • Multikey Indexes: This is unique to MongodB, it references an array and succeeds if there is a match for any value in the array.
  • Geospatial Indexes and Queries : Allows you to index GeoData. I really don’t know much about this to comment. MongoDB website is the best source.
  • Text Indexes : For full text search inside a document. Should we use lucene ? Not sure ?
  • Hashed Index: Index on hashed contents of the fields.

Indexes have properties associated with them :

  • TTL: This is a rather surprising feature. I never expected such a feature to be available on indexes, but after giving it some thought, it makes sense to expire the indexes.
  • Unique: Only documents with unique values on the field are permitted.
  • Sparse: Really useful if you are going to have sparse fields. It leaves out the documents that do not have the field.

Some more tid-bits
– The order of indexes matters.
e.g. if an index is created on (a,b,c) then index will be used only if the query is on
– A or a,b or a,b,c .
– Index will not be called if we query on b,c or c.
– It needs to be a left subset of the index.
– The command is below.
– Db.collection.ensureIndex(‘property’);
– e.g. db.students.ensureIndex({‘class’:1,’student_name’:1})
– Note: 1 or -1 is for ascending v/s descending which becomes useful when we have a lot of sort queries.
– By default all the indexes are built in the foreground i.e. all the writers will be blocked while the index is being created. It is fast but the database is blocked.
– Background ones are slow, fit for production use.
– Creating the database index in the background also blocks the current shell while it is being created.
– All indexes are Btree indexes.

Index Creation option , Unique , Removing Duplicates

– Unique indexes ensure that the key is unique in the collection.
– e.g. db.students.ensureIndex({‘student_id’:1,’class_id’:1},{‘unique’:true})
– To remove the duplicates while creating the index we can do the following :
– Provide the dropDups :true along with the unique attribute. This is dangerous, so handle with care. There is no way to control which documents it will remove. It will let live a single document and we can’t predict or configure as to which one it will be.

MultiKey Index
– It will create an index for all the items in the array if the key.
– You can’t have two multi keys in a single index. This cause a polynomial explosion of the indexes.
– We will only when we first insert something in the collection.

Index Efficiency
– $gt, $lt will use the index but the efficiency may not be there since the selectivity could be very low.
Similar for $ne etc..

Index Size
– Indexes must be kept in memory.
– Db.collection.stats() to get the stats on the collection.
Db.collection.getIndexSize() to get the total size of the index of the database.

Final thoughts
– Create any and all indexes that are required for your queries.
– Ensure that the indexes fit in memory, reading from the disk is bad.
– Sorting should also use Indexes.
– High selectivity should be the prime consideration when deciding about indexes.

MongoDB…Ops Stuff

In the previous two posts we have seen some basic querying and how to leverage the querying mechanism to get up and running. Now, we are off in the wild world and we also need to some more complicated stuff.

Creating Indexes
The api surface is really smooth with this, allowing us to specify the sort order of the indexes and the manner of building them foreground or background.

 public void CreateIndex()
var QuestionConnectionHandler = new MongoConnectionHandler<Question>("MongoDBDemo");
                          IndexKeys.Ascending("Difficulty"), IndexOptions.SetBackground(true));

Dropping indexes is also easy with


What happens when you want to see what is going on under the hood ? You let the database Explain it’s Plan.

                  .Where(q => q.Difficulty >= 3).Explain();
//or if you went the other way 
var query = Query<Question>.GTE(q => q.Difficulty, 3);
var explainPlan = QuestionConnectionHandler.MongoCollection

Now, if only we could have some stats about our database and the indexes. All wrapped in a nice syntax.

    var stats = QuestionConnectionHandler.MongoCollection.GetStats();
    Console.WriteLine("Namespace : {0}", stats.Namespace);
    Console.WriteLine("DataSize : {0}", stats.DataSize);
    Console.WriteLine("Index Count : {0}", stats.IndexCount);
    var size = QuestionConnectionHandler.MongoCollection.GetTotalDataSize();
    Console.WriteLine("The total datasize for this collection is {0}", size);

A routine task is to get all the collections in a database and all the databases on the server itself. Easy peasy!!

    var collections = QuestionConnectionHandler.MongoCollection.Database.GetCollectionNames();
    Console.WriteLine("\nThe following collections are present in the database");
    var client = new MongoClient(@"mongodb://localhost");
    var server = client.GetServer();
    var databases = server.GetDatabaseNames().ToList();
    Console.WriteLine("\nAll the databases in the server");

MongoDB C# Driver Part 3

In the previous post we have seen some simple queries. It is time we move onto something more concrete and realistic. There are basically two ways of querying MongoDB with the driver. First, as I showed last time is using LINQ. To use LINQ we need to first move into the Queryable world and then proceed with actual querying.
Be careful about pulling all the documents locally and then performing operations on them. What we really want to do is offload all our querying to MongoDB and then only use the results. The driver implements the IQueryable interface and hence we should use it.

	var result = UserConnectionHandler.MongoCollection.AsQueryable()
                                      .Where(u => u.Reputation > reputation);

The alternative to this form of querying is using a BsonDocument and a MongoQuery. The way to build up such a query is below. Note, that the first lambda is the property and the second parameter is the key for the filtering. The query builder is in the MongoDB.Drivers.Builders namespace.

var query = Query<User>.GT(u => u.Reputation, reputation);
var result = UserConnectionHandler.MongoCollection.FindAs<User>(query);

It is a bit more work to specify queries by hand so I would prefer LINQ, but both options are available.
Another, interesting querying mechanism is Regex. It was kind of hard to locate in the API ( or may be I just didn’t know where to look). It is present in Bson namesapce.

public void UserNameStartsWith(string searchKey)
var query = Query.Matches("Name", new BsonRegularExpression(string.Format("^{0}", searchKey)));
var result = UserConnectionHandler.MongoCollection.Find(query);
Console.WriteLine("We found {0} Users whose name starts with {1}", result.Count(), searchKey);

Select does not result in fewer fields being returned from the server. The entire document is pulled back and passed to the native Select method. Therefore, the projection is performed client side. We should use the IQueryable implementation from the MongoDB.Driver.Linq namespace. Alternatively, there is SetFields() that is available to selectively bring fields back from the database.

var query = Query.Matches("Name", new BsonRegularExpression(string.Format("^{0}", searchKey)));
var result = UserConnectionHandler.MongoCollection.Find(query)
     			.SetFields(Fields<User>.Include(u => u.Name, u => u.Reputation));

MongoDB C# Driver Part 2

Having done the inital work for talking to MongoDB we can now create some POCO classes and then do some querying on top of it. As usual, my model is Questions and Users.

public class Question : MongoEntity
        public string Text { get; set; }
        public string Answer { get; set; }
        public DateTime CreatedOn { get; set; }
        public int Difficulty { get; set; }
public class User : MongoEntity
        public string Name { get; set; }
        public int Reputation { get; set; }

Finally, we get down to some rela stuff.

public class SimpleQueries
        protected readonly MongoConnectionHandler<User> UserConnectionHandler;
        protected readonly MongoConnectionHandler<Question> QuestionConnectionHandler;

        public SimpleQueries()
            UserConnectionHandler = new MongoConnectionHandler<User>("MongoDBDemo");
            QuestionConnectionHandler = new MongoConnectionHandler<Question>("MongoDBDemo");

        public void CreateQuestion(Question question)
            //// Save the entity with safe mode (WriteConcern.Acknowledged)
            var result = QuestionConnectionHandler.MongoCollection.Save<Question>(question, 
                                 new MongoInsertOptions { WriteConcern = WriteConcern.Acknowledged});

            if (!result.Ok)
            else if (result.Response["err"] != null)
                Console.WriteLine("Insertion was successfull");

        public void CreateUser(User user)
            //// Save the entity with safe mode (WriteConcern.Acknowledged)
            var result = UserConnectionHandler.MongoCollection.Save<User>(user, 
                              new MongoInsertOptions { WriteConcern = WriteConcern.Acknowledged });

            if (!result.Ok)
            else if (result.Response["err"] != null)
                Console.WriteLine("Insertion was successfull");

        public void GetAllQuestions()
            var cursor = QuestionConnectionHandler.MongoCollection.AsQueryable();
            var resultSet = cursor.ToList();

            Console.WriteLine("Writing out all the questions");
            foreach (var result in resultSet)
                Console.WriteLine("Text : {0},  Answer : {1}", result.Text, result.Answer);

        public ObjectId GetOneQuestion()
            var cursor = QuestionConnectionHandler.MongoCollection.AsQueryable().FirstOrDefault();

            return cursor.Id;

        public void DeleteQuestion(ObjectId id)
            var result = QuestionConnectionHandler.MongoCollection.Remove(
                Query<Question>.EQ(e => e.Id, id), RemoveFlags.None, WriteConcern.Acknowledged);

            if (!result.Ok)
                Console.WriteLine("Delete Operation OK : {0}", result.Ok);

Now, that we have some capabilities in our application, we can query away.

//Seed Data
var question = new Question { Text = "Who are you ?", Answer = "I am MongoDB.",
                              CreatedOn = DateTime.Now, Difficulty = 3 };
var user = new User {Name = "Ashutosh", Reputation = 100};
var queries = new SimpleQueries();
var queries = new SimpleQueries();
var id = queries.GetOneQuestion();

If all is well then you will see some output and the world will be a better place.

MongoDB..C# Driver Part 1

There are several drivers available for C#. I do not plan to go thorugh all of them here. Since, the official driver now has LINQ(although not complete yet) support, we will go with it.
Basic Setup..get the stuff of NuGet. It should put in two dll’s in there
1. MongoDB.Bson
2. MongoDB.Driver

We will get to what does what later. For now assume that we only want to get some data in and out of MongoDB.
Let’s connect to MongoDB now(the code below is just quick and dirty, we will see a better version later).

//MongoDB should be running by now, and assuming you have inserted some documents in there
var client = new MongoClient(@"mongodb://localhost");
var server = client.GetServer();
var database = server.GetDatabase("YourDataBaseName");
var mongoCollection = database.GetCollection("SomeCollectionName");
//Getting all the documents
var cursor = mongoCollection.AsQueryable();

It is time for some explanataion.

What is MongoClient ?
MongoClient is the standard way of accessing the driver. I have dabbled with the python driver(pymongo) and it is the same there. I believe it was changed to keep the drivers for different languages in sync.
Reading up on this told me that SafeMode settings were dropped in favour of WriteConcern and instead of SlaveOK , ReadPreference should be used. The settings were present previously in MongoServerSettings, the new ones are on MongoClientSettings. IpV6 setting is also in MongoClientSettings.

What is MongoServer ?
The server manages the life cycle of ServerProxies. Gives access to databases and some sort of connection management. More needs to be said about the server, I will stop short for now.

Notice that I did not use a genric GetCollection here (yet, will do so soon). The generic method is also available, which we will put to use soon.

All documents in MongoDB have an Id which has the type ObjectId (ObjectId resides in MongoDB.Bson).
So, we can have an interface which takes care of this and subsequently all our types can implement this.

public interface IMongoEntity
    ObjectId Id { get; set; }
public class MongoEntity : IMongoEntity
   public ObjectId Id { get; set; }

Refinement and obtaining a better MongoDBHandler(you can do much better than what I will show you here, but that depends to large extent on your taste).

 public class MongoConnectionHandler<T> where T : IMongoEntity
        public MongoCollection<T> MongoCollection { get; private set; }
        private const string ConnectionString = @"mongodb://localhost";

        public MongoConnectionHandler(string databaseName)
            var client = new MongoClient(ConnectionString);
            var server = client.GetServer();
            var database = server.GetDatabase(databaseName);
            MongoCollection = database.GetCollection<T>(typeof (T).Name.ToLower() + "s");

Having a the *databaseName* as a parameter is very subjective, if you only have a single database then you might want to just stick it directly in the method call, otherwise it just increases the burden of the caller and spreads the database name all over the code base. Another option that comes to my mind striaghtaway is having an Enum of DatabaseName or in some sort of configuration files. The same goes for ConnectionString, put it in some place configurable. The collection names are plural so you need to just stick an extra “s” in there(this took me quite a while to figure out).
So, we are setup nicely to go forward and do some more interesting work with MongoDB.

MongoDB-Schema Design

This is a short post on Schema Design.

Schema Design in MongoDB

  • Application Driven Schema is the best way to store data in MongoDB
  • MongoDB stores rich documents i.e. store embedded documents , arrays etc..
  • Joins happen at the application level not at the database level.
  • There are no constraints in the database.
  • No support for transactions.
  • Support for Atomic present operations is present.
  • Tip : Embed as much as you can.

Relational Normalisation

  • Frees the database of modification anomalies
  • Achieve extensibility.
  • Removes bias towards a particular access pattern.
  • Constraints are supposed to be handled at the application level
  • Transactions are also not present in the database.

Mapping Strategies

Query access pattern.
Which collections will grow.

Two documents related to each other can be kept in separate collections :
To reduce the working set size of your application.
Because the combined size of the documents would be larger than 16MB

1: M

We should have true linking in this case.
i.e. have collection for each type.

If it is 1 to few then we can embed it in the 1 collection.

M: M

e.g. Student and Teacher

Generally make both of them separate collections.
Link in one direction.
At the risk of duplication we can link both ways but that should be done only when
we have performance considerations.

When to De-Normalise ?

1: 1 Embed
1 : M Embed from the many to one
M : M Link

MongoDB : Shell

MongoDB stores the documents in BSON format. It is a binary serialisation format and a super set of JSON.
It has more types than JSON which enables better integration with various languages that support these types.
Languages like perl and JS have a smaller type system which can cause problems.
ObjectId which uniquely identifies a document in mongodb is also part of the bson specification.
_id field is the primary key which is of type ObjectId.
It is immutable.
Object Id = 4-byte timestamp + machine id + process id + counter

Note : This is not comprehensive list of what mongodb can do. These are just bits that I find interesting.

MongoDb does not have a querying language like SQL. It has it’s own wire protocol with codes for doing things. The shell is basically a JS interpreter.
Here is a run down of some operators and tid-bits about them.

$regex is the operator for passing in perl lie regular expression.
$exists is for existential check.
$or is a prefix operator unlike most of the other operators.
//For querying arrays we can directly write property: value in the search criteria.
$all operator can help us match a property which should contain all of the supplied values 
Property : {$all : [value1 , value2]}
$in is the enumeration of the values for the given field.

Multiple filters on the same property must be in the same sub document or
The javascript parser will ensure that your last filter will win, since the last literal will override everything else.

db.users.find({$or: [

Upsert : This is kind of unique and took me by surprise the first time ( a pleasant one mind you ).
db.collection.update( , , , )

Quips and Quirks
Empty document selector {} matches every document in the collections. This has the effect of selecting all the documents in the collection.
From the docs

“Optional. If set to true, creates a new document when no document matches the query criteria. The default value is false, which does not insert a new document when no match is found. The syntax for this parameter depends on the MongoDB version. See Upsert Parameter.”

This is a rather useful operator once which I would dearly love SQL Server to have.

When an empty document selector is passed as an argument to the update method then mongodb will only update the first document that it finds and not all of the collection. To affect all the documents specify multi : true in your Find based operations.

                     { name: "awesome" },
                     { $inc : { age: -1 } },
                     { multi: true }

If you want to remove all the documents in a collection then use drop() instead of remove(). It is just a little faster since remove() goes through all of the documents one at a time. Further, the metadata will remain if you use remove() ( like indexes etc..) , with drop all of that will go away.
Remove() is not thread safe. Each document is however atomically removed.

If you want to find out about the last error in the database, then the runCommand is available. It can also be used to find out information about the last write performed in the database. It has a property n which gives us the number of records affected.

Db.runCommand( { getLastError : 1 }  )