How to optimize MongoDB query with both $gt and $lte?

Question

I have the following query that is kind of like a reverse range lookup: ``` db.ip_ranges.find({ $and: [{ start_ip_num: { $lte: 1204135028 } }, { end_ip_num: { $gt: 1204135028 } }] }) ``` When run with only the $lte identifier, the query returns right away. But when I run with both the $gt and $lte in the same query, it is extremely slow (in seconds). Both the start_ip_num and end_ip_num fields are indexed. How can I go about optimizing this query? EDIT I get the following when I use the explain() function on the query: ``` { "cursor" : "BtreeCursor start_ip_num_1", "nscanned" : 452336, "nscannedObjects" : 452336, "n" : 1, "millis" : 2218, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { "start_ip_num" : [ [ -1.7976931348623157e+308, 1204135028 ] ] } } ``` EDIT 2 Once I added the compound index, the explain() function returns the following: ``` { "cursor" : "BtreeCursor start_ip_num_1_end_ip_num_1", "nscanned" : 431776, "nscannedObjects" :…

Accepted Answer

So, double range queries are ill-advised in Mongo. I assume you have a single index containing both `{start_ip_num: 1, end_ip_num: 1}`.

If that doesn't get you close enough (often it is still slow if you have enough data returned by the first field, since it has to do a lot of B-tree scanning), there is one trick you can do to combat this using 2D box queries (only works for two ranges at a time).

Basically, you put a 2D geo index on a field containing the two points in an array, like [start_ip, end_ip], and give it a high enough min/max value so that it won't hit the limits which are by default just -180/180.

Finally, use a bounds query with the range going from min to the $lte value on one corner of the box, and the gt and the max value on the other corner of the box. See http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-BoundsQueries for syntax.

It looks something like this:

```
db.ip_ranges.find({ip_range:{$within:{$box:[[0, 1204135028], [1204135028, max]]}}});
```

where max is the biggest ip you can have.

It's been a while since I've looked a this, so the box might be wrong, but the concept is sound, and it made the double range queries perform a bit better than with a regular two field B-tree index. Consistently under a second (although usually a few hundred ms), compared to a few seconds with the regular index--I think I had hundreds of millions of docs at the time, but it's been a while so take these remembered benchmarks with a grain of salt. Results will vary greatly depending on your data and range sizes, I'm sure.

Update: You might want to experiment with the `bits` setting, trying a low number and a high number to see if it makes a difference. For me, it didn't seem to affect the queries on average. See http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-CreatingtheIndex for syntax.

How to optimize MongoDB query with both $gt and $lte?

Solution

Problem