MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing

1. @ #MDBlocal Tips and Tricks for Effective Indexing Alex Bevilacqua Technical Services Engineer alexbevi TORONTO

2. #MDBLocal • Your data should be accessible … quickly • When applied properly, indexes can improve performance as if by … WHY DOES THIS MATTER?

3. #MDBLocal • Working hard can suck and we all want to do less when possible • This goes for our database as well • The less index keys scanned or documents fetched from disk or cache … the better! • Let’s learn how to improve our indexes, TOGETHER! WHY DOES THIS MATTER?

4. #MDBLocal IT’S ME … MARIO! ALEX! • Application Developer • Development Lead • System Architect • Technical Services Engineer • Author • MongoDB Fanboy

5. #MDBLocal Alex Bevilacqua Technical Services Engineer Toronto Hi Phil!

6. #MDBLocal Alex Bevilacqua Technical Services Engineer Toronto I think I found some slow queries that may be missing indexes. What should I know?

7. Equality Sort Range "Rule"

8. #MDBLocal Roadmap E-S-R Overview Guidelines: • Eà R • Eà S • Sà R E-S-R Considerations Operator Type Checks Consecutive Index Keys Exceptions?

10. #MDBLocal E-S-R The ordering of index keys in a compound index is critically important.

11. #MDBLocal E-S-R The ordering of index keys in a compound index is critically important. WHY?

12. #MDBLocal E-S-R The ordering of index keys in a compound index is critically important. E-S-R provides guidance that is useful in most cases: • Equality first • Sort next • Range last

13. #MDBLocal E-S-R The ordering of index keys in a compound index is critically important. E-S-R provides guidance that is useful in most cases: • Equality first • Sort next • Range last Nope, I don’t get it. Equality? Range? ….. Explain!

14. #MDBLocal Definitions Equality Fields An exact match on a single value. For example: • {x:123} • {x:{$eq:123}} • {"x.y":123} Bounds: "[123.0, 123.0]"

15. #MDBLocal Definitions Equality Fields Sort The (entire) requested sort. .sort({a:1, b:-1}) Bounds: "[MinKey, MaxKey]", "[MinKey, MaxKey]" An exact match on a single value. For example: • {x:123} • {x:{$eq:123}} • {"x.y":123} Bounds: "[123.0, 123.0]"

16. #MDBLocal Definitions Equality Fields Sort Range Predicates Any predicates that are not exact matches. Some operators include: • {z:{$gt:9000}} • {z:{$lte:1000}} Bounds: "[9000.0, inf.0]", "[-inf.0, 1000.0]" The (entire) requested sort. .sort({a:1, b:-1}) Bounds: "[MinKey, MaxKey]", "[MinKey, MaxKey]" An exact match on a single value. For example: • {x:123} • {x:{$eq:123}} • {"x.y":123} Bounds: "[123.0, 123.0]"

18. #MDBLocal Equality Equality keys are placed first in any order If present in the query shape, equality fields should always form the prefix for the index.

19. #MDBLocal Equality Equality keys are placed first in any order If present in the query shape, equality fields should always form the prefix for the index. WHY?

20. #MDBLocal Equality Equality keys are placed first in any order If present in the query shape, equality fields should always form the prefix for the index. db.games.find( {gamertag: "Ace", score: {$gt: 9000}} )

21. #MDBLocal Equality db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) If present in the query shape, equality fields should always form the prefix for the index. Equality keys are placed first in any order

22. #MDBLocal db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) Equality {gamertag: "Ace", score: 100} {gamertag: "Ace", score: 99,999} {gamertag: "Bob", score: 15,000} {gamertag: "Bob", score: 50,000}

23. #MDBLocal db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {}{} 100 15,000 50,000 99,999 Ace Bob Bob Ace {gamertag: "Ace", score: 100} {gamertag: "Ace", score: 99,999} {gamertag: "Bob", score: 15,000} {gamertag: "Bob", score: 50,000} Equality

24. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Bob Ace db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {} {} Equality "indexBounds" : { "score" : [ "(9000.0, inf.0]" ], "gamertag" : [ "["Ace", "Ace"]" ] } db.games.createIndex({score:1, gamertag:1})

25. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Bob Ace db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {} {} Equality "indexBounds" : { "score" : [ "(9000.0, inf.0]" ], "gamertag" : [ "["Ace", "Ace"]" ] } gamertag db.games.createIndex({score:1, gamertag:1})

26. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Bob Ace db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {} {} Equality "indexBounds" : { "score" : [ "(9000.0, inf.0]" ], "gamertag" : [ "["Ace", "Ace"]" ] } gamertag score db.games.createIndex({score:1, gamertag:1})

34. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Bob Ace db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {} {} Equality

35. #MDBLocal Ace Bob Bob Ace db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) 100 99,999 15,000 50,000 {} {} {} {} Equality

36. #MDBLocal 100 Ace Bob 99,999 15,000 50,000 db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {} {} Equality "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "score" : [ "(9000.0, inf.0]" ], } db.games.createIndex({gamertag:1, score:1})

37. #MDBLocal 100 Ace Bob 99,999 15,000 50,000 db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {} {} Equality "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "score" : [ "(9000.0, inf.0]" ], } gamertag db.games.createIndex({gamertag:1, score:1})

38. #MDBLocal 100 Ace Bob 99,999 15,000 50,000 db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) {} {} {} {} Equality "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "score" : [ "(9000.0, inf.0]" ], } gamertag score db.games.createIndex({gamertag:1, score:1})

42. #MDBLocal db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) R E R E Equality before Range

44. #MDBLocal Sort fields are placed next Placing sort predicates after sequential equality keys allow for the index to: Provide a non-blocking sort. Minimize the amount of scanning required. db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort

45. #MDBLocal db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort 100 15,000 50,000 99,999 Ace Bob Bob Acegamertag score db.games.createIndex({score:1, gamertag:1})

46. #MDBLocal db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {} {} {} {} gamertag score db.games.createIndex({score:1, gamertag:1})

47. #MDBLocal db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {} {} {} {} gamertag score db.games.createIndex({score:1, gamertag:1})

48. #MDBLocal db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {1} {} {} {2} gamertag score db.games.createIndex({score:1, gamertag:1})

49. #MDBLocal db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {1} {} {} {2} gamertag score "indexBounds" : { "score" : [ "[MinKey, MaxKey]" ], "gamertag" : [ "["Ace", "Ace"]" ] } db.games.createIndex({score:1, gamertag:1})

50. #MDBLocal db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {1} {} {} {2} gamertag score "indexBounds" : { "score" : [ "[MinKey, MaxKey]" ], "gamertag" : [ "["Ace", "Ace"]" ] } db.games.createIndex({score:1, gamertag:1})

51. #MDBLocal db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {1} {} {} {2} gamertag score "indexBounds" : { "score" : [ "[MinKey, MaxKey]" ], "gamertag" : [ "["Ace", "Ace"]” ] } db.games.createIndex({score:1, gamertag:1})

54. #MDBLocal Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {1} {} {} {2} db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) gamertag score "indexBounds" : { "score" : [ "[MinKey, MaxKey]" ], "gamertag" : [ "["Ace", "Ace"]” ] } db.games.createIndex({score:1, gamertag:1})

61. #MDBLocal Sort 100 15,000 50,000 99,999 Ace Bob Bob Ace {1} {} {} {2} db.games.find( {gamertag: "Ace"} ).sort( {score: 1} )

62. #MDBLocal Sort Ace Bob Bob Ace 100 99,999 15,000 50,000 {1} {2} {} {} db.games.find( {gamertag: "Ace"} ).sort( {score: 1} )

63. #MDBLocal Sort 100 Ace Bob 99,999 15,000 50,000 {}{1} {2} {} db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) gamertag score "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], ”score" : [ "[MinKey, MaxKey]" ] } db.games.createIndex({gamertag:1, score:1})

70. #MDBLocal Sort after Equality S E S E db.games.find( {gamertag: "Ace"} ).sort( {score: 1} ) Don’t do this, it may require a full index scan

72. #MDBLocal Range fields are usually last This allows them to: Still participate in filtering the data But does not force a blocking sort. db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) Range

73. #MDBLocal db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) R E R E Range after Equality

74. #MDBLocal db.games.find( {gamertag: "Ace", score: {$gt: 9000}} ) R E R E Range after Equality Should range come after sort too?

75. #MDBLocal R E R E Range after Equality db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Should range come after sort too?

76. #MDBLocal R E R E Range db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1})

77. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Bob Ace * db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range gamertag score db.games.createIndex({ …, score:1, gamertag:1})

78. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Bob Ace * db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range gamertag score db.games.createIndex({ …, score:1, gamertag:1})

79. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Cali Ace * db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range gamertag score db.games.createIndex({ …, score:1, gamertag:1})

80. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Cali Ace * db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range {} {} {} {}{1}{2} {3} gamertag score db.games.createIndex({ …, score:1, gamertag:1})

81. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Cali Ace * {} {2} {3} {1} db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range gamertag score "indexBounds" : { *, "score" : [ "(9000.0, inf.0]" ], "gamertag" : [ "[MinKey, MaxKey]" ] } db.games.createIndex({ …, score:1, gamertag:1})

91. #MDBLocal {2} {3} {1} db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range Blocking SortBlocking Sort BS

92. #MDBLocal {2}{3}{1} db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range BS

93. #MDBLocal 100 15,000 50,000 99,999 Ace Bob Cali Ace * {} {2} {3} {1} db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range

94. #MDBLocal db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range * Ace Bob Cali Ace 100 15,000 50,00099,999 {} {1} {2} {3}

95. #MDBLocal * 100 15,000 50,00099,999 Ace Bob Cali db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range {} {1} {3} score gamertag "indexBounds" : { *, "gamertag" : [ "[MinKey, MaxKey]" ] "score" : [ "(9000.0, inf.0]" ] } db.games.createIndex({ …, gamertag:1, score:1}) {2}

106. #MDBLocal BS R S R S db.games.find({*, score: {$gt: 9000}}).sort({gamertag: 1}) Range after Sort

108. #MDBLocal Operator Type Check Some operators are simple $eq: Equality $gte: Range Others are trickier

109. #MDBLocal Operator Type Check - Inequality Inequality Operators: E, S, or R? • $ne:123 • $nin:[1,2] Range!

110. #MDBLocal Operator Type Check - Inequality Inequality Operators: E, S, or R? • $ne:123 • $nin:[1,2] Range! But … how do you know?

111. #MDBLocal Operator Type Check - Inequality Inequality Operators: E, S, or R? • $ne:123 • $nin:[1,2] Range! "indexBounds" : { "x" : [ "[MinKey, 123.0)", "(123.0, MaxKey]" ] }

112. #MDBLocal Operator Type Check - Inequality Range! "indexBounds" : { "x" : [ "[MinKey, 123.0)", "(123.0, MaxKey]" ] } 50 123 999

115. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range!

117. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! planecar racecar

120. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! p???ecar racecar

121. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! p???ecar racecar

123. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! raincarry

124. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! raincarry

125. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! raincarry Carpool

128. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! raincarry Carpoo l Woah Woah WOAH …. Since the regex is left anchored, can’t we skip the middle tree?

129. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! raincarry Carpoo l Woah Woah WOAH …. Since the regex is left anchored, can’t we skip the middle tree?

130. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! cArrycarry Carpoo l Woah Woah WOAH …. Since the regex is left anchored, can’t we skip the middle tree?

131. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! cArrycarry Carpoo l Woah Woah WOAH …. Since the regex is left anchored, can’t we skip the middle tree?

132. #MDBLocal Operator Type Check - Regex Regex Operators: E, S, or R? • {str:/car/} • {str:/^car/i} Range! cArrycarry Carpoo l

133. #MDBLocal Operator Type Check - $in $in filters: E, S, or R? • {field:{$in:[1,3]}} … it depends with respect to key ordering Alone: a series of Equality matches Combined: possibly a Range

134. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}})

135. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}) "indexBounds" : { "field" : [ "[1.0, 1.0]", "[3.0, 3.0]" ] } ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

141. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}) .sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", "[3.0, 3.0]" ] } ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

142. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}) .sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", "[3.0, 3.0]" ], "sortField" : [ "[MinKey, MaxKey]" ] } ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

146. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}) .sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", "[3.0, 3.0]" ], "sortField" : [ "[MinKey, MaxKey]" ] } ? 1 2 3 C G B F A D BS db.coll.createIndex({field:1, sortField:1})

147. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}) .sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", "[3.0, 3.0]" ], "sortField" : [ "[MinKey, MaxKey]" ] } ? 1 2 3 C G B F A D BS db.coll.createIndex({field:1, sortField:1})

148. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}) .sort({sortField:1}) ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

149. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}).sort({sortField:1}) ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

150. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}).sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", ], "sortField" : [ "[MinKey, MaxKey]" ] } ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

155. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}).sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", ], "sortField" : [ "[MinKey, MaxKey]" ] } "indexBounds" : { "field" : [ "[3.0, 3.0]", ], "sortField" : [ "[MinKey, MaxKey]" ] } ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

156. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}).sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", ], "sortField" : [ "[MinKey, MaxKey]" ] } "indexBounds" : { "field" : [ "[3.0, 3.0]", ], "sortField" : [ "[MinKey, MaxKey]" ] } ? 1 2 3 C G B F A D ? 1 2 3 C G B F A D db.coll.createIndex({field:1, sortField:1})

159. #MDBLocal Operator Type Check - $in .find({field:{$in:[1,3]}}).sort({sortField:1}) "indexBounds" : { "field" : [ "[1.0, 1.0]", ], "sortField" : [ "[MinKey, MaxKey]" ] } "indexBounds" : { "field" : [ "[3.0, 3.0]", ], "sortField" : [ "[MinKey, MaxKey]" ] } ? 1 2 3 C G B F A D ? 1 2 3 C G B F A D {2} {4} {1} {3} Sort Merge db.coll.createIndex({field:1, sortField:1})

160. #MDBLocal BS S M Operator Type Check - $in

162. #MDBLocal Consecutive Index Keys Given Indexes: {gamertag:1, date:1, game:1} {gamertag:1, game:1, date:1} Which one is “better”? It depends on the query shape(s)! Consider the query: .find({ gamertag:"Ace", game: "Halo” })

163. #MDBLocal Consecutive Index Keys .find({ gamertag:"Ace", game: "Halo” })

164. #MDBLocal Consecutive Index Keys Ace Mario Halo 2016 2017 Mario Halo 2018 2019 .find({ gamertag:"Ace", game: "Halo” }) db.games.createIndex({gamertag:1, date:1, game:1})

165. #MDBLocal Consecutive Index Keys Ace Mario Halo 2016 2017 Mario Halo 2018 2019 .find({ gamertag:"Ace", game: "Halo” }) {} {} {} {} db.games.createIndex({gamertag:1, date:1, game:1})

166. #MDBLocal Consecutive Index Keys Ace Mario Halo 2016 2017 Mario Halo 2018 2019 .find({ gamertag:"Ace", game: "Halo” }) "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "date" : [ "[MinKey, MaxKey]" ], "game" : [ "["Halo", "Halo"]" ] } {} {} {} {} db.games.createIndex({gamertag:1, date:1, game:1})

172. #MDBLocal Consecutive Index Keys Ace Mario Halo 2016 2017 Mario Halo 2018 2019 .find({ gamertag:"Ace", game: "Halo” }) {} {} "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "date" : [ "[MinKey, MaxKey]" ], "game" : [ "["Halo", "Halo"]" ] } {} {} db.games.createIndex({gamertag:1, date:1, game:1})

177. #MDBLocal Consecutive Index Keys .find({ gamertag:"Ace", game: "Halo” }) {} {} {} {} "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "date" : [ "[MinKey, MaxKey]" ], "game" : [ "["Halo", "Halo"]" ] } Halo Ace Mario Halo 2016 2017 Mario 2018 2019 "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "game" : [ "["Halo", "Halo"]" ] } db.games.createIndex({gamertag:1, game:1})

178. #MDBLocal Ace 2016 2017Mario2018 2019Halo Consecutive Index Keys .find({ gamertag:"Ace", game: "Halo” }) "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "game" : [ "["Halo", "Halo"]" ] } "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "game" : [ "["Halo", "Halo"]" ], "date" : [ "[MinKey, MaxKey]" ] } "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "game" : [ "["Halo", "Halo"]" ] } {}{} {}{} db.games.createIndex({gamertag:1, game:1, date:1})

179. #MDBLocal R E E E E E E Consecutive Index Keys R Look familiar? This is equality after range!

181. #MDBLocal Exceptions Is the E-S-R “rule” always optimal? Nope. Consider the following query: db.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1})

182. #MDBLocal Exceptionsdb.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1}) Ace 1 5,000 9,0012 9,000 … … 2001 2000 2015 2019 2019 gamertag score date {} {} {} {1} {2} db.games.createIndex({gamertag:1, score:1, date:1})

183. #MDBLocal Exceptionsdb.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1}) Ace 1 5,000 9,0012 9,000 … … 2001 2000 2015 2019 2019 gamertag score date {} {} {} db.games.createIndex({gamertag:1, score:1, date:1}) "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "score" : [ "[MinKey, MaxKey]" ], "date" : [ "[2019.0, inf.0]" ] } {1} {2}

197. #MDBLocal Exceptionsdb.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1}) Ace 1 5,000 9,0012 9,000 … … 2001 2000 2015 2019 2019 gamertag score date {} {} {} "executionStats" : { "nReturned" : 2, "executionTimeMillis" : 23, "totalKeysExamined" : 9001, "totalDocsExamined" : 2, db.games.createIndex({gamertag:1, score:1, date:1}) {1} {2}

198. #MDBLocal Exceptionsdb.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1}) Ace 1 5,000 9,0012 9,000 … … 2001 2000 2015 2019 2019

199. #MDBLocal Exceptionsdb.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1}) Ace 2001 2000 2015 2019 2019 {score:2} {score:1} {score:5000} {score:9001} {score:9000} … … gamertag date

200. #MDBLocal Exceptionsdb.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1}) Ace … … 2000 2001 2015 2019 2019 {score:2} {score:1} {score:5000} {score:9001} {score:9000} gamertag date db.games.createIndex({gamertag:1, date:1}) "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "date" : [ "[2019.0, inf.0]" ] }

205. #MDBLocal Exceptionsdb.games .find({gamertag:"Ace", date:{$gte:2019}}) .sort({score:1}) Ace … … 2000 2001 2015 2019 2019 {score:2} {score:1} {score:5000} gamertag date {score:9001} {score:9000} BS "executionStats" : { "nReturned" : 2, "executionTimeMillis" : 0, "totalKeysExamined" : 2, "totalDocsExamined" : 2, "executionStats" : { "nReturned" : 2, "executionTimeMillis" : 0, "totalKeysExamined" : 2, "totalDocsExamined" : 2, "executionStages" : { "stage" : "SORT", "memUsage" : 154, "memLimit" : 33554432, db.games.createIndex({gamertag:1, date:1}) "indexBounds" : { "gamertag" : [ "["Ace", "Ace"]" ], "date" : [ "[2019.0, inf.0]" ] }

206. #MDBLocal Exceptions ……… … BS S R E R E

207. Summary

208. #MDBLocal E-S-R Guidance A good starting place applicable to most use cases Place keys in the following order: Equality first Sort next Range last Remember: • Some operators may be range instead of equality • Having consecutive keys used in the index is important • Specifics about your data set may need a different approach

209. Thank You!

MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing

Recommended

More Related Content

What's hot (20)

Similar to MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing (20)

More from MongoDB (20)

Recently uploaded (20)

MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing