title: mongo db design for fast queries on ranges
body: <p>Currently, I have a mongoDb collection with documents of the following type (~1000 docs):</p>
  
  <pre><code>{   _id : "...",
       id : "0000001",
     gaps : [{start:100, end:110}, {start:132, end:166}...], // up to ~1k elems
    bounds: [68, 88, 126, 228...],                           // up to 100 elems
    length: 300,
      seq : "ACCGACCCGAGGACCCCTGAGATG..."
  }
  </code></pre>
  
  <p>"gaps" and "bounds" values in the array refer to coordinates in "seq" and "length" refers to "seq" length.
  I have defined indexes on "id", "gaps" and "bounds".
  I need to query based on coordinates ranges. For example given "from=100" and "to=200" I want to retrieve for each document a sub-array of "gaps", a sub-array of "bounds" and the "seq" substring that lay inside the range (between 100 and 200 in this case).
  Right now, this is done using the following aggregation pipeline:</p>
  
  <pre><code>    db.annot.aggregate(
           {$match  : {"id" : "000001"}},
           {$unwind : "$gaps"},
           {$unwind : "$bounds"},
           {$match: {
                      $or :[
                           {"gaps.start" : {$gte:from, $lte:to}},
                           {"gaps.end" : {$gte:from, $lte:to}},
                           {"bounds" : {$gte:from, $lte:to}}
                      ]
                    }
           },
           {$project:{
                   id:1,
                   gaps:1,
                   bounds:1,
                   subseq:{$substr:["$seq", from, (to-from)]}}},
           {$group : {
                   _id : "$id",
                   gaps : {"$addToSet" : "$gaps"},
                   bounds : {"$addToSet" : "$bounds"},  
                   subseq : {"$first" : "$subseq"}}},
      )                                                                                                                         
  </code></pre>
  
  <ul>
  <li>Querying the whole collection (leaving out the first "$match" in the pipeline) takes ~14 seconds.</li>
  <li>Querying individually all the documents <strong>sequentially</strong> takes at most 50msec each (~19 secs in total).</li>
  <li>Querying individually all the documents <strong>in parallel</strong> takes in total ~7 secs.</li>
  <li>Querying with a pipeline that only matches the id (ie, using the first "$match" in the pipeline) takes ~5 secs in total.</li>
  </ul>
  
  <p>What would be the best db and query design to maximize the performance of this kind of queries?</p>