StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreating MongoDB composite indexes on multiple fields, with some overlap?
text
Body
copied!<p>I'm new to MongoDB and trying to make sure I set up the proper indexes. I've seen similar questions about composite indexes here but none that exactly cover the situation I'm in. </p> <p><strong>Note:</strong> I'm using Rails 3.2 and Mongoid.</p> <p>I have a collection of Events that are always going to be sorted (and often searched on) date, but generally with another parameter as well. For example, I might want to find the Events that match a particular set of categories within a certain date range; or I might want to find the Events that match a particular person within a certain date range. The types of searches will be:</p> <ol> <li>Always by date (or at least sorting by date)</li> <li>Often by category</li> <li>Sometimes additionally by [person, venue, or keyword]</li> </ol> <p>The first solution I came up with was multiple composite keys that all start with date and category, like so:</p> <pre><code>class Event ... index ([ [:date, Mongo::DESCENDING], [:category_id, Mongo::ASCENDING] ["people.person_id", Mongo::ASCENDING] ]) index ([ [:date, Mongo::DESCENDING], [:category_id, Mongo::ASCENDING] [:venue_id, Mongo::ASCENDING] ]) index ([ [:date, Mongo::DESCENDING], [:category_id, Mongo::ASCENDING] [:keywords, Mongo::ASCENDING] ]) </code></pre> <p>But it seems a little funny to me to keep overlapping the "date + category_id" index, and also what about the cases when I'm <em>not</em> searching on category_id?</p> <p><strong>UPDATE:</strong> dcrosta asked what kind of queries would be running, and how frequently. Without knowing exactly, I can guess that it would look something like the following:</p> <p><em>Very frequent:</em></p> <ul> <li>by date </li> <li>by date + category</li> <li>by date + keyword </li> <li>by date + category + keyword</li> </ul> <p><em>Somewhat frequent:</em></p> <ul> <li>by date + person</li> <li>by date + venue </li> </ul> <p><em>Less frequent:</em></p> <ul> <li>by date + category + venue</li> <li>by date + category + person</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload