MongoDB: Use several single indexes or a compound index
<p>I am a beginner at MongoDB.
I am using version 3.2.
I read in several places that MongoDB can use only one index in a query, but the pieces of information I found seem a little bit outdated, and I couldn't find something in the official docs.</p>

<p>I have a collection of ~500M products with this form:</p>

<pre><code>{_id: ObjectId('574d92332a2b10d7618b4575'), title: A, category_id: ObjectId('574d92332a2b10d7618b4575'), price: 30.23, rating:5 },
{_id: ObjectId('574d92332a2b10d7618b4575'), title: B, category_id: ObjectId('574d92332a2b10d7618b4575'), price: 20.23, rating:3 },
{_id: ObjectId('574d92332a2b10d7618b4575'), title: C, category_id: ObjectId('574d92332a2b10d7618b4575'), price: 10.23, rating:4 }
</code></pre>

<p>I need to find all products per category, and sort it by rating, then by price, but the final user may also be wanting to just sort it by price directly.</p>

<p>Every single query will need the category_id to be passed, it is compulsery.</p>

<p>I created 3 indexes: <code>{category_id:1}</code>, <code>{rating:1}</code> and <code>{price:1}</code>.</p>

<p><strong>These queries are fast:</strong></p>

<p><em>Most expensive products per category</em>
<code>db.products.find({category_id:ObjectId('574d92332a2b10d7618b4575')}).sort({price:-1})</code></p>

<p><em>Best products per category</em>
<code>db.products.find({category_id:ObjectId('574d92332a2b10d7618b4575')}).sort({rating:-1})</code></p>

<p><em>Worst products per category</em>
<code>db.products.find({category_id:ObjectId('574d92332a2b10d7618b4575')}).sort({rating:1})</code></p>

<p><strong>But this query is incredibly slow</strong></p>

<p><em>Best products per category, then cheapest</em>
<code>db.products.find({category_id:ObjectId('574d92332a2b10d7618b4575')}).sort({rating:-1, price:1})</code></p>

<p>If you were me, which indexes would you create, and why?
I'm starting to think that having <code>price</code> and <code>rating</code> alone is stupid, because every query will need the <code>category_id</code>, so maybe my indexes should include <code>category_id</code>, but <a href="https://docs.mongodb.com/manual/core/index-compound/#sort-order" rel="nofollow">what confuses me is the last paragraph of the official doc about compound indexes</a>.</p>

<p>I already <a href="https://docs.mongodb.com/manual/indexes/" rel="nofollow">read this whole section on the official page of MongoDB</a> but I can't find an answer to my specific problem.</p>