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, Sep 10 '14 at 05:50

yyunikov · Accepted Answer · 2014-09-10T06:01:46.870

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 '14 at 06:01

answered Sep 10 '14 at 05:50

yyunikov

5,719
2
43
78

1

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)? – Anand Sep 10 '14 at 06:17
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. – yyunikov Sep 10 '14 at 06:22
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? – Anand Sep 10 '14 at 07:09
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 Sep 10 '14 at 07:30
If you find this answer useful, please accept it as the best answer. – yyunikov Sep 10 '14 at 09:41
I will accept..Just one last query which I have updated in my question..can you please answer it? – Anand Sep 10 '14 at 09:43
It is hard to say in terms of SQL, but I think that compound index of all 3 should work. However, I'm not sure, and would definitely check my mongo query with the explain() command to see if index is used as I described above. – yyunikov Sep 10 '14 at 09:47
I have updated my post with the actual mongodb retrieval using Java for which i want to create indexes – Anand Sep 10 '14 at 09:48
As I already said, check it in the command line using explain(). I don't think that your compound index will be used here. For this case I would create some other index. – yyunikov Sep 10 '14 at 09:57

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

1 Answers1