title: MongoDB Find performance: single compound index VS two single field indexes
body: <p>I'm looking for an advice about which indexing strategy to use in MongoDb 3.4.  </p>
  
  <p>Let's suppose we have a <em>people</em> collection of documents with the following shape:  </p>
  
  <pre><code>{
      _id: 10,
      name: "Bob",
      age: 32,
      profession: "Hacker"
  }
  </code></pre>
  
  <p>Let's imagine that a web api to query the collection is exposed and that the only possibile filters are by <em>name</em> or by <em>age</em>.<br>
  A sample call to the api will be something like:  <code>http://myAwesomeWebSite/people?name="Bob"&amp;age=25</code></p>
  
  <p>Such a call will be translated in the following query: <code>db.people.find({name: "Bob", age: 25})</code>.  </p>
  
  <p>To better clarify our scenario, consider that: </p>
  
  <ul>
  <li>the field <em>name</em> was already in our documents and we already have an index on that field</li>
  <li>we are going to add the new field <em>age</em> due to some new features of our application</li>
  <li>the database is only accessible via the web api mentioned above and the most important requirement is to expose a super fast web api </li>
  <li>all the calls to the web api will apply a filter <strong>on both the fields name and age</strong> (put another way, all the calls to the web api will have the same pattern, which is the one showed above) </li>
  </ul>
  
  <p>That said, we have to decide which of the following indexes offer the best performance:</p>
  
  <ul>
  <li>One compound index: <code>{name: 1, age: 1}</code></li>
  <li>Two single-field indexes: <code>{name: 1}</code> and <code>{age: 1}</code></li>
  </ul>
  
  <p>According to some simple tests, it seems that <strong>the single compound index is much more performant than the two single-field indexes</strong>.  </p>
  
  <p>By executing a single query via the mongo shell, the explain() method suggests that using a single compound index you can query the database nearly ten times faster than using two single fields indexes.  </p>
  
  <p><strong>This difference seems to be less drammatic in a more realistic scenario, where instead of executing a single query via the mongo shell, multiple calls are made to two different urls of a nodejs web application</strong>. Both urls execute a query to the database and return the fetched data as a json array, one using a collection with the single compound index and the other using a collection with two single-field indexes (both collections having exactly the same documents).<br>
  In this test the single compound index still seems to be the best choice in terms of performance, but this time the difference is less marked.  </p>
  
  <p>According to test results, we are considering to use the single compound index approach.  </p>
  
  <p>Does anyone has experience about this topic ? Are we missing any important consideration (maybe some disadvantage of big compound indexes) ?</p>