MongoDB Indexing Part 2


Multi Term Query

db.animals.find({name:’cat’, tags:’land’}).explain()

— Looking for the name ‘cat and the tag ‘land’. the result

vlcsnap-2016-10-06-05h11m34s177

As you can see there is an index and the same index is being used.

Index can also be used on ranged queries

Mongodb indexing for ranged queries

Mongodb indexing for ranged queries

This an alphabetic string comparison. Uses the same index. On the index bounds it says find me something between the empty string and dog

MongoDB ranged index using less than and explain plan

MongoDB ranged index using less than and explain plan

The initial plain method shows that quesry returns the results o two animals less than a donkey. Of which one animal “cat ” lives on land. The explain method shows that it still used the index on the name field. It indexed 2 documents but returned only one object by using the balance of the query terms.

Drop Index

To drop an index , we must first find the name of the index by looking at the system indexes. We can see that thee is one index named “name_1”

vlcsnap-2016-10-06-05h39m35s664

Drop Index can be done thus

db.animals.dropindex(:name_1″)

{“nIndexeswas : 2, “ok” :1}

I you try to dop an index that does not exist Mongo will throw an error saying “Index not found”. Now that we have dropped the index, the only index left is on the _ID field. This index cannot be dropped. Every document will have an index on the ID field and every collection will maintain an index on the id field for all the documents in the database.

Nested Fields

Your documents are typically more complex than first level fields . They can have sub documents arrays,. Mongo Supports indexing of arbitrarily nested fields. This is not true for other NoSQL databases and certainly not true or relational databases. For example if you create a blob in a relational database, it won’t let you index on individual elements in the blob.

MongoDB indexing on nested fields, sub documents & arrays

MongoDB indexing on nested fields, sub documents & arrays

MongoDB indexing on nested fields, sub documents & arrays

MongoDB shows indexing on color and returns 2 indexes

Indexing on an array (Multi Key Indexing)

The screen shots below are self explanatory

MongoDB indexing on array

MongoDB create indexing on array

MongoDB explain plan for an array.

MongoDB indexing on array, 3 objects returnedj

Sorting using Index

MongoDB sorting on the index

MongoDB sorting on the index

In the query above we are sorting on the tags, but notice that “scan order” is false, that is because we are scanning for tags and there is no need for further scan.

MongoDB sorting on the index but different column

MongoDB sorting on the index but different column

In the above query we are scanning the document based on the name instead of the index tag. Notice that this time the scanandorder is set to true. It still uses the tag index, because it is a quick way to get to the document but this time around the index is not useful fir sorting.

Create  Unique Index

MongoDB creating unique Index

MongoDB creating unique Index

Create unique index on the name field by specifying unique : true. Also note that it hasn’t displayed the default unique index  of the ID field. This is implied and hence not displayed.

Now if we tried to insert a duplicate, Mongo would complian

Mongo DB create unique index

Mongo DB create unique index

Sparse Index

MongoDB create Sparse index

MongoDB create Sparse index

The database has exactly 6 animals , 4 of which has a color field specified. For very large document repositories the index will make an attempt create an  entry  for every document and it will save null if the key does not exist.

A sparse index is an index that only stores entries for documents that have that field. See syntax above for creating a sparse index. Now query for the color red and it returns one red animal (cat)

MongoDB : Create a sparse index

MongoDB : Create a sparse index

We have to be very careful here, We created a sparse index on the color field. Lets see how many ocean animals doe we have? But what If I wanted to sort them by their color. That just returns 2 results. Doing an explain tells us that when you just look for the tag “Ocean”, it doesn’t use any index, it just visits all the documents.

MongoDB : Sorting on Sparse index

MongoDB : Sorting on Sparse index

Now when we sort on the color, it uses the sparse index we created,  and since the sparse index does not contain entries for animals that does not have an info.color field, one of the records was omitted.

MongoDB : Sorting on Sparse index without the field

MongoDB : Sorting on Sparse index without the info field

The color field for Penguin is missing and hence it is not included in the index and since mongo used that index exclusively to identify  the documents the document got left out. This is something to be aware of when using sparse index. There could be other indexes on other fields, so complex queries need to be analyzed and you need to make sure that your indexing strategies and your queries match up to all the documents you want.

Compound indexes

So far we have indexed only on one field, but we can index on multiple fields, deep fields, shallow fields

MongoDB: Compound Indexes

MongoDB: Compound Indexes

We create an index on Tags field which is an array and then the name which is a string field. Running a query with two terms will result in the two indexes being used. If however you were to query on the same terms but in reverse order i.e. tags,name, we will still get the same results.

But suppose you used only one of the terms in the index, e.x. the name

MongoDB : Compound index using one term or index

MongoDB : Compound index using one term or index

You will notice that it does not use the index, recall that the index was created on the tags and then the name. Index is deemed useful if the terms in the index, match the index definition from let t right.

 

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the companies Search engine rankings)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s