Question on index direction

Kumaresan_Vasagam · 2024-08-23T12:02:21.474Z

I am following the example of leaderboard on this page - https://www.mongodb.com/docs/v7.0/core/indexes/index-types/index-compound/sort-order/
My index →

db.leaderboard.createIndex( { score: -1, username: 1 } )

My query and the executionStats:

db.leaderboard.find({score{$lte:60}}).sort({username:-1}).explain("executionStats").executionStats
{
  executionSuccess: true,
  nReturned: 5,
  executionTimeMillis: 0,
  totalKeysExamined: 5,
  totalDocsExamined: 5,
  executionStages: {
    stage: 'FETCH',
    nReturned: 5,
    executionTimeMillisEstimate: 0,
    works: 12,
    advanced: 5,
    needTime: 6,
    needYield: 0,
    saveState: 0,
    restoreState: 0,
    isEOF: 1,
    docsExamined: 5,
    alreadyHasObj: 0,
    inputStage: {
      stage: 'SORT',
      nReturned: 5,
      executionTimeMillisEstimate: 0,
      works: 12,
      advanced: 5,
      needTime: 6,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      sortPattern: { username: -1 },
      memLimit: 33554432,
      type: 'default',
      totalDataSizeSorted: 386,
      usedDisk: false,
      spills: 0,
      spilledDataStorageSize: 0,
      inputStage: {
        stage: 'IXSCAN',
        nReturned: 5,
        executionTimeMillisEstimate: 0,
        works: 6,
        advanced: 5,
        needTime: 0,
        needYield: 0,
        saveState: 0,
        restoreState: 0,
        isEOF: 1,
        keyPattern: { score: -1, username: 1 },
        indexName: 'score_-1_username_1',
        isMultiKey: false,
        multiKeyPaths: { score: [], username: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { score: [ '[60, -inf.0]' ], username: [ '[MinKey, MaxKey]' ] },
        keysExamined: 5,
        seeks: 1,
        dupsTested: 0,
        dupsDropped: 0
      }
    }
  }
}

My query sorts the username in descending order (-1), the results are sorted in descending indeed. But what I am trying to make sense is that the “direction” field in the “executionStats” says “forward”, but the index is username in ascending order.
Could someone explain this?

emmanuel_bernard · 2024-08-23T13:16:23.634Z

Hi @Kumaresan_Vasagam,

In most cases, applying the ESR (Equality, Sort, Range) Rule to arrange the index keys helps to create a more efficient compound index.

Here is a MongoDB resource

https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-rule/

Your index should be db.leaderboard.createIndex( { username: -1,score: 1 } )