Indexes ☝️

Table Of Contents

• How We Need to Think Differently
• Basic Indexes
  • Get Indexes
  • Create Index
    • Index In Action
  • Delete Index

How We Need to Think Differently

"Data that is accessed together should be stored together." - MongoDB

Basic Indexes

Get Indexes

// Default Index On Id 
cooker> db.recipes.getIndexes()

[ { v: 2, key: { _id: 1 }, name: '_id_' } ]

Create Index

• Create index on title

cooker> db.recipes.createIndex({"title" : 1}, {"name" : "title_index", "unique" : true});

title_index

cooker> db.recipes.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { title: 1 }, name: 'title_index', unique: true }
]

Index In Action

• executionStats.totalDocsExamined = 1 instead N (All)

cooker> db.recipes.find({"title" : "Apple Pie"}, {"title" : 1})

[ { _id: ObjectId("5edf1cd43260aab97ea0d588"), title: 'Apple Pie' } ]

• Execution Stats

cooker> db.recipes.find({"title" : "Apple Pie"}, {"title" : 1}).explain("executionStats");
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'cooker.recipes',
    indexFilterSet: false,
    parsedQuery: { title: { '$eq': 'Apple Pie' } },
    maxIndexedOrSolutionsReached: false,
    maxIndexedAndSolutionsReached: false,
    maxScansToExplodeReached: false,
    winningPlan: {
      stage: 'PROJECTION_SIMPLE',
      transformBy: { title: 1 },
      inputStage: {
        stage: 'FETCH',
        inputStage: {
          stage: 'IXSCAN',
          keyPattern: { title: 1 },
          indexName: 'title_index',
          isMultiKey: false,
          multiKeyPaths: { title: [] },
          isUnique: true,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: { title: [ '["Apple Pie", "Apple Pie"]' ] }
        }
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1,
    executionTimeMillis: 0,
    totalKeysExamined: 1,
    totalDocsExamined: 1,
    executionStages: {
      stage: 'PROJECTION_SIMPLE',
      nReturned: 1,
      executionTimeMillisEstimate: 0,
      works: 2,
      advanced: 1,
      needTime: 0,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      transformBy: { title: 1 },
      inputStage: {
        stage: 'FETCH',
        nReturned: 1,
        executionTimeMillisEstimate: 0,
        works: 2,
        advanced: 1,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        docsExamined: 1,
        alreadyHasObj: 0,
        inputStage: {
          stage: 'IXSCAN',
          nReturned: 1,
          executionTimeMillisEstimate: 0,
          works: 2,
          advanced: 1,
          needTime: 0,
          needYield: 0,
          saveState: 0,
          restoreState: 0,
          isEOF: 1,
          keyPattern: { title: 1 },
          indexName: 'title_index',
          isMultiKey: false,
          multiKeyPaths: { title: [] },
          isUnique: true,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: { title: [ '["Apple Pie", "Apple Pie"]' ] },
          keysExamined: 1,
          seeks: 1,
          dupsTested: 0,
          dupsDropped: 0
        }
      }
    }
  },
  command: {
    find: 'recipes',
    filter: { title: 'Apple Pie' },
    projection: { title: 1 },
    '$db': 'cooker'
  },
  serverInfo: {
    host: '49d4ff683a97',
    port: 27017,
    version: '5.0.9',
    gitVersion: '6f7dae919422dcd7f4892c10ff20cdc721ad00e6'
  },
  serverParameters: {
    internalQueryFacetBufferSizeBytes: 104857600,
    internalQueryFacetMaxOutputDocSizeBytes: 104857600,
    internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600,
    internalDocumentSourceGroupMaxMemoryBytes: 104857600,
    internalQueryMaxBlockingSortMemoryUsageBytes: 104857600,
    internalQueryProhibitBlockingMergeOnMongoS: 0,
    internalQueryMaxAddToSetBytes: 104857600,
    internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600
  },
  ok: 1
}

Delete Index

cooker> db.recipes.dropIndex("title_index");
{ nIndexesWas: 2, ok: 1 }

cooker> db.recipes.getIndexes()
[ { v: 2, key: { _id: 1 }, name: '_id_' } ]