Create an index in mongodb with more than one field using java

Question

I am new to MongoDB. By default, collections in mongodb have index on _id field.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 that 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 am not sure if the above is correct. Can someone please confirm? If not correct,how can i correct it ?

Adding to the above, for retrieval i am doing something like this:

        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 fast retrieval like above should i create index separately on field1,field2 and field3? or compound index on all the 3 fields?

Yes. What you have done is correct. You have created a composite Index with the two fields. — BatScream
– BatScream, Commented Sep 10, 2014 at 5:50

yyunikov · Accepted Answer · 2014-09-10 06:01:46Z

4

This is correct and is called Compound Index. You have created an index on two fields: field_1 (ascending) and field_2 (descending). So for example if you will sort by field_1 ascending and field_2 descending or by field_1 descending and field_2 ascending, MongoDb will use this index field_1_1_field_2_-1 for it.

However, the above index cannot support sorting by ascending field_1 values and then by ascending field_2 values. Also if you search or sort only on field_2, it will not use this index. In this case you can create a separate indexes for field_1 and field_2.

To check if your query is using index, try to use explain() and see which cursor is used. For example, you can try such query:

db.your_collection.find({ "field_1" : "something" }).explain()

If you will see that the cursor which is used is BtreeCursor, then the query uses the index, in case of BasicCursor it does not.

For more information, just refer to this article.

edited Sep 10, 2014 at 6:01

answered Sep 10, 2014 at 5:50

yyunikov

5,9175 gold badges47 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Anand Over a year ago

Thanks Yuriy..i may want to search only for field_2 so do i need to create a separate index for that? Do you mean to say this index will only work when i search for field_1 and field_2 combined(as a whole)?

yyunikov Over a year ago

Yes, if you need to search only for field_2, then you need a separate index for it, but don't forget that writes will be slower. This index will work for field_1 and field_2 combined, but not in all cases as I described above and also it should work if you search by field_1 only.

Anand Over a year ago

I read that indexes slow down inserts..so considering both write and read operations will occur on equal proportions, should we go for indexes or not?

yyunikov Over a year ago

That's right, all depends and your db structure and queries you make :) A lot of factors depend on this. At least you can see what are you doing more: if reads - then you definitely need an index, and if writes - you need to think if you need them or not.

yyunikov Over a year ago

If you find this answer useful, please accept it as the best answer.

|

Collectives™ on Stack Overflow

Create an index in mongodb with more than one field using java

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related