Compound Indexes MongoDB Java Example

Compound Indexes MongoDB Java Example

Having a requirement on searchingIndexing in MongoDB multiple keys in a query or need the result in the sorted order on the basis of multiple keys this is where Compound Indexes come into the picture. If we look at our previous post example – Simple Index MongoDB Java Example,where we have created index on key username such that whenever we performs a query on userName key it always gives us the record in the optimal time and in sorted order of username. Now if I want my result to be in ascending order of age and descending order of username then in that case it won’t be optimized as results are not stored in the ascending order of age because we don’t have any index on age key.

To achieve optimization on the sorting we have to create index on both the keys age and username. These types of indexes where index is applied on more than 1 key are known as Compound Indexes. As stated above these are useful in the scenarios when you want to pass more than 1 key in the search criteria or in sort criteria.

How to create compound index : You can create compound indexes using ensureIndex() and pass all the keys on which you want to have an index.
e.g :

db.collectionName.ensureIndex({“age”:1,”username”:1});

In the below mentioned example we have compared the sorting performance in both the cases when only single index on age is applied and then compound index on age and userName is applied.

/**
 * 
 */
package com.techidiocy.mongo.indexing;

import java.util.logging.Logger;

import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
import com.techidiocy.mongo.MongoUtils;

/**
 * @author yoMongo
 *
 */
public class CompoundIndexes {
	private static final Logger log = Logger.getAnonymousLogger();
	private static final String DEFAULT_COLLECTION_NAME="yoMongo";
	
	/**
	 * indexed only on the age
	 */
	private static void sortingWithSingleIndex(){
		DBObject searchObject = new BasicDBObject();
		searchObject.put("age", new BasicDBObject().append("$gte", 40).append("$lte", 50));
		DBObject sortCriteria = new BasicDBObject();
		sortCriteria.put("age", 1);
		sortCriteria.put("userName", -1);
		MongoUtils.getCollection(DEFAULT_COLLECTION_NAME).ensureIndex("age");
		DBObject explainObject = MongoUtils.getCollection(DEFAULT_COLLECTION_NAME).find(searchObject).sort(sortCriteria).explain();
		log.info("sorting without Index--->"+explainObject);
	}
	
	/**
	 * indexed on age and username
	 */
	private static void sortingWithCompoundIndex(){
		DBObject searchObject = new BasicDBObject();
		searchObject.put("age", new BasicDBObject().append("$gte", 40).append("$lte", 50));
		DBObject sortCriteria = new BasicDBObject();
		sortCriteria.put("age", 1);
		sortCriteria.put("userName", -1);
		DBObject indexKeys = new BasicDBObject();
		indexKeys.put("age", 1);
		indexKeys.put("userName", -1);
		MongoUtils.getCollection(DEFAULT_COLLECTION_NAME).ensureIndex(indexKeys);
		DBObject explainObject = MongoUtils.getCollection(DEFAULT_COLLECTION_NAME).find(searchObject).sort(sortCriteria).explain();
		log.info("sorting with Index--->"+explainObject);
	}

	
	public static void main(String[] args) {
		MongoUtils.insertMillionRecords();
		MongoUtils.getCollection(DEFAULT_COLLECTION_NAME).dropIndexes();
		CompoundIndexes.sortingWithSingleIndex();
		MongoUtils.getCollection(DEFAULT_COLLECTION_NAME).dropIndexes();
		CompoundIndexes.sortingWithCompoundIndex();
		MongoUtils.getCollection(DEFAULT_COLLECTION_NAME).drop();
	}

}

Explain Output:
Simple Index Case :

{ 
 "cursor" : "BtreeCursor age_1" ,
 "isMultiKey" : false ,
 "n" : 109806 ,
 "nscannedObjects" : 109806 ,
 "nscanned" : 109806 ,
 "nscannedObjectsAllPlans" : 109806 ,
 "nscannedAllPlans" : 109806 ,
 "scanAndOrder" : true ,
 "indexOnly" : false ,
 "nYields" : 1715 ,
 "nChunkSkips" : 0 ,
 "millis" : 1418 ,
 "indexBounds" : { "age" : [ [ 40 ,
 50]]} ,
 "allPlans" : [ { "cursor" : "BtreeCursor age_1" ,
 ...
 }

Compound Index Case :

{ 
 "cursor" : "BtreeCursor age_1_userName_-1" ,
 "isMultiKey" : false ,
 "n" : 109806 ,
 "nscannedObjects" : 109806 ,
 "nscanned" : 109806 ,
 "nscannedObjectsAllPlans" : 109806 ,
 "nscannedAllPlans" : 109806 ,
 "scanAndOrder" : false ,
 "indexOnly" : false ,
 "nYields" : 857 ,
 "nChunkSkips" : 0 ,
 "millis" : 189 ,
 "indexBounds" : { "age" : [ [ 40 ,
 50]] ,
 "userName" : [ [ { "$maxElement" : 1} ,
 { "$minElement" : 1}]]} 
 ...
 }

Compare the highlighted fields in both the cases.

cursor: talking about the applied index on the collection which is different in both the cases.
n: number of documents matching our search criteria.
nscannedObjects: Number of objects scanned in the collection to find the matching documents.
scanAndOrder : In 1st case it is true as sorting happened in internal memory after search result is returned and in 2nd case it is false as we already have an index over the userName key.
millis : Total time taken for searching and sorting.

When you execute the main method you will see that total time taken in the 1st case when only single index on age was available it took more time in comparison to when index is available on both the keys. In both the cases searching will take the same time as our search criteria contains only the age key as search criteria and we have an index also on that ,difference will come while sorting as in the 1st case it has to do an in memory sorting before it returns us the result while in the latter case documents are already sorted by both age and userName.

Complete project can be downloaded from here.
Image Credits: http://mongodb.org


 

Let'sConnect

Saurabh Jain

A Developer working on Enterprise applications ,Distributed Systems, Hadoop and BigData.This blog is about my experience working mostly on Java technologies ,NoSQL ,git , maven and Hadoop ecosystem.
Let'sConnect

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS

One thought on “Compound Indexes MongoDB Java Example

Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>