Collection._ensureIndex creating several indexes?

performance
1

I’m trying to understand my database indexing. When starting, my server runs:

Videos._ensureIndex(
  {
    _id: 1,
    owner_id: 1,
    ts: 1,
    likesCount: 1,
    repostsCount: 1,
    published: 1,
    isPrivate: 1,
    readyToPlay: 1,
    order: 1,
    favoritesCount: 1,
    deleted: 1,
    isSlice: 1,
  },
  {
      name: 'videoIndex',
  }
);

When I connect to my mongo shell and run db.videos.getIndexes() I get:

[
  {
    "v" : 1,
    "key" : {
      "_id" : 1
    },
    "name" : "_id_",
    "ns" : "pie.videos"
  },
  {
    "v" : 1,
    "key" : {
      "_id" : 1,
      "owner_id" : 1,
      "ts" : 1,
      "likesCount" : 1,
      "repostsCount" : 1
    },
    "name" : "_id_1_owner_id_1_ts_1_likesCount_1_repostsCount_1",
    "ns" : "pie.videos",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "_id" : 1,
      "owner_id" : 1,
      "ts" : 1,
      "likesCount" : 1,
      "repostsCount" : 1,
      "published" : 1,
      "isPrivate" : 1,
      "readyToPlay" : 1,
      "order" : 1
    },
    "name" : "_id_1_owner_id_1_ts_1_likesCount_1_repostsCount_1_published_1_isPrivate_1_readyToPlay_1_order_1",
    "ns" : "pie.videos",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "_id" : 1,
      "owner_id" : 1,
      "ts" : 1,
      "likesCount" : 1,
      "repostsCount" : 1,
      "published" : 1,
      "isPrivate" : 1,
      "readyToPlay" : 1,
      "order" : 1,
      "featuredScore" : 1
    },
    "name" : "_id_1_owner_id_1_ts_1_likesCount_1_repostsCount_1_published_1_isPrivate_1_readyToPlay_1_order_1_featuredScore_1",
    "ns" : "pie.videos",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "_id" : 1,
      "owner_id" : 1,
      "ts" : 1,
      "likesCount" : 1,
      "repostsCount" : 1,
      "published" : 1,
      "processingComplete" : 1
    },
    "name" : "_id_1_owner_id_1_ts_1_likesCount_1_repostsCount_1_published_1_processingComplete_1",
    "ns" : "pie.videos",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "_id" : 1,
      "owner_id" : 1,
      "ts" : 1,
      "likesCount" : 1,
      "repostsCount" : 1,
      "published" : 1,
      "readyToPlay" : 1
    },
    "name" : "_id_1_owner_id_1_ts_1_likesCount_1_repostsCount_1_published_1_readyToPlay_1",
    "ns" : "pie.videos",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "_id" : 1,
      "owner_id" : 1,
      "ts" : 1,
      "likesCount" : 1,
      "repostsCount" : 1,
      "published" : 1,
      "readyToPlay" : 1,
      "order" : 1
    },
    "name" : "_id_1_owner_id_1_ts_1_likesCount_1_repostsCount_1_published_1_readyToPlay_1_order_1",
    "ns" : "pie.videos",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "owner_id" : 1,
      "ts" : 1
    },
    "name" : "owner_id_1_ts_1",
    "ns" : "pie.videos",
    "background" : true
  },
  {
    "v" : 1,
    "key" : {
      "_id" : 1,
      "owner_id" : 1,
      "ts" : 1,
      "likesCount" : 1,
      "repostsCount" : 1,
      "published" : 1,
      "isPrivate" : 1,
      "readyToPlay" : 1,
      "order" : 1,
      "featuredScore" : 1,
      "deleted" : 1
    },
    "name" : "videoIndex",
    "ns" : "pie.videos"
  }
]

I’m trying to understand what that means. I was expecting an index per field I’m asking for, but I’m getting a number of indexes that each concerns a group of fields. I’m not sure why that is.

I’ve sometimes changed the fields I’m asking for in _ensureIndex so I’m wondering if each time I changed that this added a new group, or if this is the expected form of my index, and if so what that means exactly.

2

Hmm, this looks wrong. Unless you’re actually making find calls like this:

Videos.find({
  _id: 'abc',
  owner_id: 'abc',
  ts: '?',
  likesCount: { $gt: 10 },
  repostsCount: { $gt: 20 },
  // etc

What you’ve done is made a compound index that’s optimized when searching the database for one or more of those fields (in that order). This is probably not what you want.

This is recommended reading.

A good rule of thumb for making indices is, you’ll typically want one for each frequently-used query. So if you’re doing this a lot:

const vids = Videos.find({ owner_id: someString });

and

const vids = Videos.find({ owner_id: someString, likesCount: { $gt: 0 } });

Then you’ll want to make a compound index that will cover both queries:

Videos._ensureIndex({ owner_id: 1, likesCount: 1 });
3 Likes
3

Also, get Compass, it makes things like indices a lot easier to read.

2 Likes
4

I’m only indexing fields that are used somewhere in selections or sorts. However they might not be all used at the same time. Should I do things differently? I’ll try compass

5

Yes, hit the “recommended reading” link above, grab a coffee, and block out at least 30-45 mins of your time. :slight_smile: I learned a lot by going through those docs on indices.

6

Compound indexes cover the compound as well as individual / partial compound queries,

7

You can try making a compound index and one with partial/single field that is in the compound index, the query will still hit the compound index coverage, not the single

8

Thanks! Yeah it’s very different from MYSQL that I knew better. Coffee was worth it :slight_smile:

1 Like
9

I came from a PHP/MySQL background too, so getting into Node.js and MongoDB was a bit of a shock! :wink: