Code Summary: Optimizing Read Operations by Using Query Settings

Use setQuerySettings to Specify an Index

Here’s an example of using the setQuerySettings to specify an index by including it in the array of allowedIndexes:

db.adminCommand({
    setQuerySettings: {
    find: "listingsAndReviews",
    filter: { 
        minimum_nights: { $lte: 3 }, 
        property_type: 'House',
        room_type: 'Entire home/apt'
        },
    $db: 'sample_airbnb'
    },
    settings: {
        indexHints: {
              ns: { 
            db: 'sample_airbnb', 
            coll: 'listingsAndReviews' 
            },
            allowedIndexes: 
                ['property_type_1_room_type_1_minimum_nights_1' ]
     }
  }
})

Use setQuerySettings to Restrict Indexes

Here’s an example of using the setQuerySettings to exclude an index by not including it in the array of allowedIndexes:

db.adminCommand({
    setQuerySettings: {
        find: "listingsAndReviews",
        filter: { 
            minimum_nights: { $lte: 3 }, 
            property_type: 'House',
            room_type: 'Entire home/apt'
        },
    $db: 'sample_airbnb'
    },
    settings: {
        indexHints: {
            ns: { 
                db: 'sample_airbnb', 
                coll: 'listingsAndReviews' 
            },
            allowedIndexes: [
                'property_type_1_minimum_nights_1',
                'room_type_1_minimum_nights_1',
                'minimum_nights_1' 
            ]
        }
    }
})

Remove Query Settings with the removeQuerySettings Command

Supply the Query Shape

You can remove query settings by supplying the query shape to the removeQuerySettings command. Here’s an example:

db.adminCommand({
    removeQuerySettings: {
        find: "listingsAndReviews",
        filter: { 
            minimum_nights: { $lte: 3 }, 
            property_type: 'House',
            room_type: 'Entire home/apt'
        },
        $db: 'sample_airbnb'
    }
})

Supply the Query Hash

You can also remove query settings by supplying the query shape hash to the removeQuerySettings command.

To retrieve the query shape hash, run the following command to retrieve all of the querySettings:

db.aggregate([{ $querySettings: {} }])
[
  {
    queryShapeHash: 'C1EC3943F5F3A1613406BABFE2D3687B1D83889450A62CC5F476BDC8EB5368AE',
    settings: {
      indexHints: [
        {
          ns: { db: 'sample_airbnb', coll: 'listingsAndReviews' },
          allowedIndexes: [
            'property_type_1_minimum_nights_1',
            'room_type_1_minimum_nights_1',
            'minimum_nights_1'
          ]
        }
      ]
    },
    representativeQuery: {
      find: 'listingsAndReviews',
      filter: {
        minimum_nights: { '$lte': 3 },
        property_type: 'House',
        room_type: 'Entire home/apt'
      },
      '$db': 'sample_airbnb'
    }
  }
]

Once you have the query shape hash, you can remove the query settings as seen in this example:

db.adminCommand({
	removeQuerySettings: 
	'C1EC3943F5F3A1613406BABFE2D3687B1D83889450A62CC5F476BDC8EB5368AE'
	}
})