(MongoDB) How MongoDB Selects an Index

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 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 each for these indexes. In three parallel threads, MongoDB will run the query such that each one will use 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, something like in the graphic below. 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.

TODO: NEED A GRAPHIC HERE.

To win the race, a query thread must be the first to either return all query results or return a threshold number of results (e.g., 100 documents) 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 knows 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 or add or drop an index. Finally, the query plan cache does not survive a restart of a mongod process.

this article is from MongoDB:The Definitive Guide,3rd Edition