I have a mongo collection of IpRanges, with start_address and end_address. I'd like to query given an IP. with compound index on {start_address:1, end_address:1}, this however is still very slow:
db.ip_blocks.find({start_address: {$lte: 1665637698}, end_address: {$gte: 1665637698}})
with .explain(), it appeared that this query causes a lot of scanning and slow, as only the $lte query worked hard, and the second query is scanning what's remaining from the first query.
Here is a trick to speed this up if you are only trying to get the first match:
db.ip_blocks.find({start_address: {$lte: 1665637698}, end_address: {$gte: 1665637698}}).limit(1)
This doesn't cause scan.
However, if you are expecting more than one match, limit(2) or above will cause a full scan.
So to make it work for multiple matches, here is the second trick:
Add this index:
{start_address:-1, end_address:1}
Query with a $maxScan parameter:
db.ip_blocks.find({start_address:{$lte: 1665637698}, end_address:{$gte: 1665637698}})._addSpecial("$maxScan", 100)
As the addresses are ordered in a way helping us to scan, the correct records can be retrieved and also with limited scanning and thus it becomes fast.
