Create index in mongodb with more than one field using java
I am new to MongoDB
. By default, collections in MongoDB
have an index on the field _id
. I need to create an index on 2 more fields using Java.
DBObject indexOptions = new BasicDBObject();
indexOptions.put(field_1, 1);
indexOptions.put(field_2, -1);
collection.createIndex(indexOptions )
When I query this in mongodb using db.collection_name.getIndexes ()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "schema.collection_name"
},
{
"v" : 1,
"key" : {
"field_1" : 1,
"field_2" : -1
},
"name" : "field_1_1_field_2_-1",
"ns" : "schema.collection_name"
}
]
I'm not sure if the above is correct. Can someone please confirm? If not correct, how can I fix it?
Adding to the above, I do something like this to retrieve:
DBObject subscriberIdObj = new BasicDBObject( field3, value3 );
DBObject subscriberIdIsNullObj = new BasicDBObject( field3, null );
BasicDBList firstOrValues = new BasicDBList();
firstOrValues.add( subscriberIdObj );
firstOrValues.add( subscriberIdIsNullObj );
DBObject firstOr = new BasicDBObject( "$or", firstOrValues );
DBObject batchIdObj = new BasicDBObject( field1, value1 );
DBObject fileNameObj = new BasicDBObject( field2, value2 );
BasicDBList secondOrValues = new BasicDBList();
secondOrValues.add( batchIdObj );
secondOrValues.add( fileNameObj );
DBObject secondOr = new BasicDBObject( "$or", secondOrValues );
BasicDBList andValues = new BasicDBList();
andValues.add( firstOr );
andValues.add( secondOr );
DBObject query = new BasicDBObject( "$and", andValues );
DBCursor cursor = mongoDatastore.getDB().getCollection(collection_name).find(query);
Now, for quick retrieval like above, should I create an index separately on field1, field2 and field3? or a composite index for all three fields?
source to share
This is rightly called Compound Index
. You have created an index in two fields: field_1
(ascending) and field_2
(descending). For example, if you sort in field_1
ascending and field_2
descending order or field_1
descending and field_2
ascending order, MongoDb will use that index field_1_1_field_2_-1
for it.
However, the above index cannot support sorting by ascending values field_1
and then ascending field_2
. Also if you do a search or sort on only field_2
, it won't use that index. In this case, you can create separate indexes for field_1
and field_2
.
To check if your query is using an index try using explain()
and see which cursor is being used. For example, you can try a query like this:
db.your_collection.find({ "field_1" : "something" }).explain()
If you see that the cursor being used is this BtreeCursor
, then the query uses the index, and in case it BasicCursor
does not.
For more information, just refer to this article .
source to share