how to create index mongodb properly?

Question

let say i have this huge documents.

2 of them got this array of object;

{
  status: "A",
  group: "public",
  "created.dt": ....
}

{
  status: "A",
  group: "private",
  "created.dt": ....
}

i indexed and ensure like this :

db.collection.ensureIndex({"created.dt":-1});

db.collection.ensureIndex({"created.dt":-1, "status":1});
db.collection.ensureIndex({"created.dt":-1, "group":1});

db.collection.ensureIndex({"created.dt":-1, "status":1, "group":1});

Query:

db.collection.find(
{
  "status": { 
    $in: ["A", "I"] 
  },
  "asset_group": "public"
},
{
  sort: {
   'created.dt':1
  }
}
).count();

is it wrong ?

after i make this index still slow. please help me proper index.thank you

Answer 1

for the following query:

db.collection.find(
{
  "status": { 
    $in: ["A", "I"] 
  },
  "asset_group": "public"
},
{
  sort: {
   'created.dt':1
  }
}
).count();

The best index will be this:

db.collection.ensureIndex({"status":1, "asset_group":1, "created.dt":1});

or

db.collection.ensureIndex({"asset_group":1, "status":1, "created.dt":-1});

Since you are querying on

status, asset_group - these values can be switched in the index prefix

and sort on created.dt field - therefore created.at shuold be the last value in the index prefix. Note: On sort the index can traverse the reverse order.

For other queries, other indexes might be more suitable. Read more about compound indexes.