Skip to content

Instantly share code, notes, and snippets.

@whardier

whardier/gist:1239654

Created September 24, 2011 18:12
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save whardier/1239654 to your computer and use it in GitHub Desktop.
Save whardier/1239654 to your computer and use it in GitHub Desktop.
Download ZIP
MongoDB Min/Max Example (With accompanying annoyingly large but useful index)
Raw
gistfile1.js
# The data
> db.minmax.find()
{ "_id" : ObjectId("4e7e1814f836a6cd3f414909"), "ichi" : 13046, "ni" : 28664, "san" : 16392 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490a"), "ichi" : 21565, "ni" : 6487, "san" : 23549 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490b"), "ichi" : 21904, "ni" : 29534, "san" : 15345 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490c"), "ichi" : 22511, "ni" : 4158, "san" : 32184 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490d"), "ichi" : 16347, "ni" : 22774, "san" : 3930 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490e"), "ichi" : 26574, "ni" : 2486, "san" : 17466 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490f"), "ichi" : 29113, "ni" : 16052, "san" : 17271 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414910"), "ichi" : 25451, "ni" : 3725, "san" : 24723 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414911"), "ichi" : 4100, "ni" : 10417, "san" : 2738 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414912"), "ichi" : 11583, "ni" : 14490, "san" : 7095 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414913"), "ichi" : 16707, "ni" : 13706, "san" : 10153 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414914"), "ichi" : 22329, "ni" : 29916, "san" : 20296 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414915"), "ichi" : 9092, "ni" : 15239, "san" : 17162 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414916"), "ichi" : 25871, "ni" : 16389, "san" : 6073 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414917"), "ichi" : 734, "ni" : 21047, "san" : 16734 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414918"), "ichi" : 5383, "ni" : 2350, "san" : 22608 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414919"), "ichi" : 10744, "ni" : 31019, "san" : 6083 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491a"), "ichi" : 4049, "ni" : 25847, "san" : 8495 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491b"), "ichi" : 6305, "ni" : 31528, "san" : 10387 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491c"), "ichi" : 19180, "ni" : 28157, "san" : 15378 }
has more
#OK now lets grab the first 10 for page 1 (without a filter)
> db.minmax.find().limit(10).explain()
{
"cursor" : "BasicCursor",
"nscanned" : 10,
"nscannedObjects" : 10,
"n" : 10,
"millis" : 1,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
}
}
> db.minmax.find().limit(10)
{ "_id" : ObjectId("4e7e1814f836a6cd3f414909"), "ichi" : 13046, "ni" : 28664, "san" : 16392 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490a"), "ichi" : 21565, "ni" : 6487, "san" : 23549 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490b"), "ichi" : 21904, "ni" : 29534, "san" : 15345 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490c"), "ichi" : 22511, "ni" : 4158, "san" : 32184 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490d"), "ichi" : 16347, "ni" : 22774, "san" : 3930 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490e"), "ichi" : 26574, "ni" : 2486, "san" : 17466 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41490f"), "ichi" : 29113, "ni" : 16052, "san" : 17271 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414910"), "ichi" : 25451, "ni" : 3725, "san" : 24723 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414911"), "ichi" : 4100, "ni" : 10417, "san" : 2738 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414912"), "ichi" : 11583, "ni" : 14490, "san" : 7095 }
#OK Now lets grab everything AFTER the last object using min vs offset
> db.minmax.find().min({ "_id" : ObjectId("4e7e1814f836a6cd3f414912"), "ichi" : 11583, "ni" : 14490, "san" : 7095 }).limit(11).explain()
{
"cursor" : "BtreeCursor _id_1_ichi_1_ni_1_san_1",
"nscanned" : 11,
"nscannedObjects" : 11,
"n" : 11,
"millis" : 1,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"start" : {
"_id" : ObjectId("4e7e1814f836a6cd3f414912"),
"ichi" : 11583,
"ni" : 14490,
"san" : 7095
},
"end" : {
"_id" : {
"$maxElement" : 1
},
"ichi" : {
"$maxElement" : 1
},
"ni" : {
"$maxElement" : 1
},
"san" : {
"$maxElement" : 1
}
}
}
}
> db.minmax.find().min({ "_id" : ObjectId("4e7e1814f836a6cd3f414912"), "ichi" : 11583, "ni" : 14490, "san" : 7095 }).limit(11)
{ "_id" : ObjectId("4e7e1814f836a6cd3f414912"), "ichi" : 11583, "ni" : 14490, "san" : 7095 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414913"), "ichi" : 16707, "ni" : 13706, "san" : 10153 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414914"), "ichi" : 22329, "ni" : 29916, "san" : 20296 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414915"), "ichi" : 9092, "ni" : 15239, "san" : 17162 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414916"), "ichi" : 25871, "ni" : 16389, "san" : 6073 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414917"), "ichi" : 734, "ni" : 21047, "san" : 16734 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414918"), "ichi" : 5383, "ni" : 2350, "san" : 22608 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414919"), "ichi" : 10744, "ni" : 31019, "san" : 6083 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491a"), "ichi" : 4049, "ni" : 25847, "san" : 8495 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491b"), "ichi" : 6305, "ni" : 31528, "san" : 10387 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491c"), "ichi" : 19180, "ni" : 28157, "san" : 15378 }
#Drop the first.. we have our page 2 results. In most testing it was cheaper to drop the first than it was to use greater than expressions. However the following works fine as well
> db.minmax.find({"_id" : { $gt: ObjectId("4e7e1814f836a6cd3f414912")} }).min({ "_id" : ObjectId("4e7e1814f836a6cd3f414912"), "ichi" : 11583, "ni" : 14490, "san" : 7095 }).limit(10).explain()
{
"cursor" : "BtreeCursor _id_1_ichi_1_ni_1_san_1",
"nscanned" : 11,
"nscannedObjects" : 10,
"n" : 10,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"start" : {
"_id" : ObjectId("4e7e1814f836a6cd3f414912"),
"ichi" : 11583,
"ni" : 14490,
"san" : 7095
},
"end" : {
"_id" : {
"$maxElement" : 1
},
"ichi" : {
"$maxElement" : 1
},
"ni" : {
"$maxElement" : 1
},
"san" : {
"$maxElement" : 1
}
}
}
}
> db.minmax.find({"_id" : { $gt: ObjectId("4e7e1814f836a6cd3f414912")} }).min({ "_id" : ObjectId("4e7e1814f836a6cd3f414912"), "ichi" : 11583, "ni" : 14490, "san" : 7095 }).limit(10)
{ "_id" : ObjectId("4e7e1814f836a6cd3f414913"), "ichi" : 16707, "ni" : 13706, "san" : 10153 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414914"), "ichi" : 22329, "ni" : 29916, "san" : 20296 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414915"), "ichi" : 9092, "ni" : 15239, "san" : 17162 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414916"), "ichi" : 25871, "ni" : 16389, "san" : 6073 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414917"), "ichi" : 734, "ni" : 21047, "san" : 16734 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414918"), "ichi" : 5383, "ni" : 2350, "san" : 22608 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414919"), "ichi" : 10744, "ni" : 31019, "san" : 6083 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491a"), "ichi" : 4049, "ni" : 25847, "san" : 8495 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491b"), "ichi" : 6305, "ni" : 31528, "san" : 10387 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491c"), "ichi" : 19180, "ni" : 28157, "san" : 15378 }
#Now where this really shines is when you are searching.
> db.minmax.find({san: { $gte: 5000} }).limit(10).explain()
{
"cursor" : "BtreeCursor san_1",
"nscanned" : 10,
"nscannedObjects" : 10,
"n" : 10,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"san" : [
[
5000,
1.7976931348623157e+308
]
]
}
}
> db.minmax.find({san: { $gte: 5000} }).limit(10)
{ "_id" : ObjectId("4e7e1814f836a6cd3f414a7c"), "ichi" : 19016, "ni" : 32724, "san" : 5017 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41498d"), "ichi" : 26765, "ni" : 22608, "san" : 5024 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414add"), "ichi" : 13307, "ni" : 29336, "san" : 5024 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414a4d"), "ichi" : 17791, "ni" : 24163, "san" : 5062 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41495c"), "ichi" : 22852, "ni" : 976, "san" : 5216 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414a13"), "ichi" : 8520, "ni" : 8192, "san" : 5219 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414be5"), "ichi" : 14576, "ni" : 23510, "san" : 5268 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414a75"), "ichi" : 25717, "ni" : 3051, "san" : 5316 }
{ "_id" : ObjectId("4e7e1815f836a6cd3f414ce7"), "ichi" : 7650, "ni" : 14442, "san" : 5364 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414a69"), "ichi" : 952, "ni" : 29845, "san" : 5368 }
# Page 1.. now for page 2
> db.minmax.find({san: { $gte: 5000} }).min({ "_id" : ObjectId("4e7e1814f836a6cd3f414914"), "ichi" : 22329, "ni" : 29916, "san" : 20296 }).limit(11).explain()
{
"cursor" : "BtreeCursor _id_1_ichi_1_ni_1_san_1",
"nscanned" : 13,
"nscannedObjects" : 11,
"n" : 11,
"millis" : 0,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {
"start" : {
"_id" : ObjectId("4e7e1814f836a6cd3f414914"),
"ichi" : 22329,
"ni" : 29916,
"san" : 20296
},
"end" : {
"_id" : {
"$maxElement" : 1
},
"ichi" : {
"$maxElement" : 1
},
"ni" : {
"$maxElement" : 1
},
"san" : {
"$maxElement" : 1
}
}
}
}
> db.minmax.find({san: { $gte: 5000} }).min({ "_id" : ObjectId("4e7e1814f836a6cd3f414914"), "ichi" : 22329, "ni" : 29916, "san" : 20296 }).limit(11)
{ "_id" : ObjectId("4e7e1814f836a6cd3f414914"), "ichi" : 22329, "ni" : 29916, "san" : 20296 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414915"), "ichi" : 9092, "ni" : 15239, "san" : 17162 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414916"), "ichi" : 25871, "ni" : 16389, "san" : 6073 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414917"), "ichi" : 734, "ni" : 21047, "san" : 16734 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414918"), "ichi" : 5383, "ni" : 2350, "san" : 22608 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414919"), "ichi" : 10744, "ni" : 31019, "san" : 6083 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491a"), "ichi" : 4049, "ni" : 25847, "san" : 8495 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491b"), "ichi" : 6305, "ni" : 31528, "san" : 10387 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491c"), "ichi" : 19180, "ni" : 28157, "san" : 15378 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f41491d"), "ichi" : 17724, "ni" : 32485, "san" : 5761 }
{ "_id" : ObjectId("4e7e1814f836a6cd3f414920"), "ichi" : 13478, "ni" : 5250, "san" : 25447 }
#throw away the first. Note the awesome amounts of happy index scanning.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment