How MongoDB Selects an Index

Now let’s take a look at how MongoDB chooses an index to satisfy a query. Let’s imagine we have five indexes. When a query comes in, MongoDB looks at the query’s shape. The shape has to do with what fields are being searched on and additional information, such as whether or not there is a sort. Based on that information, the system identifies a set of candidate indexes that it might be able to use in satisfying the query.

Let’s assume we have a query come in, and three of our five indexes are identified as candidates for this query. MongoDB will then create three query plans, one for each of these indexes, and run the query in three parallel threads, each using a different index. The objective here is to see which one is able to return results the fastest.

Visually, we can think of this as a race, as pictured in the below figure. The idea here is that the first query plan to reach a goal state is the winner. But more importantly, going forward it will be selected as the index to use for queries that have that same query shape. The plans are raced against each other for a period (referred to as the trial period), after which the results of each race are used to calculate the overall winning plan.

To win the race, a query thread must be the first to either return all the query results or return a trial number of results in sort order. The sort order portion of this is important given how expensive it is to perform in-memory sorts.

The real value of racing several query plans against one another is that for subsequent queries that have the same query shape, the MongoDB server will know which index to select. The server maintains a cache of query plans. A winning plan is stored in the cache for future use for queries of that shape. Over time, as a collection changes and as the indexes change, eventually a query plan might be evicted from the cache and MongoDB will, again, experiment with possible query plans to find the one that works best for the current collection and set of indexes. Other events that will lead to plans being evicted from the cache are if we rebuild a given index, add or drop an index, or explicitly clear the plan cache. Finally, the query plan cache does not survive a restart of a mongod process.