Databases
StackOverflow2013
Postsdbo
POMongoDb: how to create the right (composite) index for data with many searchable fields
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMongoDb: how to create the right (composite) index for data with many searchable fields
    text
    copied!<p>UPDATE: I need to add that the point of this question is to allow me to define schemas for Json Rest Stores. The user can search by any one key, or several keys. So, I cannot easily predict what the users will search by -- it could be 1, 2, 5 fields (this is especially true for data-rich fields like people, bookings, etc.)</p> <p>Imagine that I have an index as such:</p> <pre><code>{ "item": 1, "location": 1, "stock": 1 } </code></pre> <p>Following the <a href="http://docs.mongodb.org/manual/core/index-compound/#index-type-compound" rel="nofollow">MongoDb manual on indexes</a>:</p> <blockquote> <p>MongoDB can use this index to support queries that include:</p> <ul> <li>the item field,</li> <li>the item field and the location field,</li> <li>the item field and the location field and the stock field, or</li> <li>only the item and stock fields; however, this index would be less efficient than an index on only item and stock.</li> </ul> <p>MongoDB cannot use this index to support queries that include:</p> <ul> <li>only the location field,</li> <li>only the stock field, or</li> <li>only the location and stock fields.</li> </ul> </blockquote> <p>Now, suppose I have a schema with exactly these fields:</p> <ul> <li>item: String</li> <li>location: String</li> <li>stock: String</li> <li>qty: number</li> </ul> <p>And imagine I want to make sure every query is indeed indexed. I would do:</p> <p>For <code>item</code>:</p> <ul> <li><code>item, location, stock, qty</code></li> <li><code>item, location, qty, stock</code></li> <li><code>item, stock, qty, location</code></li> <li><code>item, stock, location, qty</code></li> <li><code>item, qty, location, stock</code></li> <li><code>item, qty, stock, location</code></li> </ul> <p>For <code>location</code>:</p> <ul> <li>...you know the gist</li> </ul> <p>Now... this seems a little insane. If you have a database where you have TEN searchable fields, this becomes clearly unworkable as the number of indexes grows exponentially.</p> <p>Am I missing something? My idea was to define a schema, define which fields were searchable, and write a function that makes up all of the needed indexes regardless of what fields were present and what fields weren't. However, I am thinking about it, and... well, I must be missing something.</p> <p>Am I?</p>
 

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