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

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

Index can also be used on 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

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”

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.

Indexing on an array (Multi Key Indexing)

The screen shots below are self explanatory

Sorting using 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.

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

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

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)

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.

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.

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

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

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)