CODEWITHSUNDEEP
javamongodbindexingmongodb-indexes
Anand
Anand

Reputation: 21310

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

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?

Upvotes: 3

Views: 2549

Answers (1)

yyunikov
yyunikov

Reputation: 5897

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.

Upvotes: 4

Related Questions