Norberto Leite, profile picture
Uploaded byNorberto Leite
PPTX, PDF309 views

Avoid Query Pitfalls

The document discusses common query pitfalls in MongoDB, emphasizing the importance of query optimization for better performance and resource utilization. It highlights specific examples of how indexing can affect query execution time, particularly when dealing with sorting and blocking operations. Furthermore, it addresses various concerns stakeholders may have regarding query performance and offers insights into effective index usage to improve query efficiency.

Embed presentation

Download to read offline
1 / 113
2 / 113
3 / 113
4 / 113
5 / 113
6 / 113
7 / 113
8 / 113
9 / 113
10 / 113
11 / 113
12 / 113
13 / 113
14 / 113
15 / 113
16 / 113
17 / 113
18 / 113
19 / 113
20 / 113
21 / 113
22 / 113
23 / 113
24 / 113
25 / 113
26 / 113
27 / 113
28 / 113
29 / 113
30 / 113
31 / 113
32 / 113
33 / 113
34 / 113
35 / 113
36 / 113
37 / 113
38 / 113
39 / 113
40 / 113
41 / 113
42 / 113
43 / 113
44 / 113
45 / 113
46 / 113
47 / 113
48 / 113
49 / 113
50 / 113
51 / 113
52 / 113
53 / 113
54 / 113
55 / 113
56 / 113
57 / 113
58 / 113
59 / 113
60 / 113
61 / 113
62 / 113
63 / 113
64 / 113
65 / 113
66 / 113
67 / 113
68 / 113
69 / 113
70 / 113
71 / 113
72 / 113
73 / 113
74 / 113
75 / 113
76 / 113
77 / 113
78 / 113
79 / 113
80 / 113
81 / 113
82 / 113
83 / 113
84 / 113
85 / 113
86 / 113
87 / 113
88 / 113
89 / 113
90 / 113
91 / 113
92 / 113
93 / 113
94 / 113
95 / 113
96 / 113
97 / 113
98 / 113
99 / 113
100 / 113
101 / 113
102 / 113
103 / 113
104 / 113
105 / 113
106 / 113
107 / 113
108 / 113
109 / 113
110 / 113
111 / 113
112 / 113
113 / 113
Avoiding Query Pitfalls @nleite
2 Agenda Common Query Pitfalls How to think about and how to solve them Tips and Tricks for Query Optimization
3 Howdy! Disclaimer Who's this guy? Norberto Leite Lead Engineer @nleite MongoDB
https://university.mongodb.com/
M201: MongoDB Performance
6 https://usatthebiglead.files.wordpress.com/2012/10/dez-drop-10-1-12.gif?w=1000 What's the story about query pitfalls?
7 The Power of Query Optimization Query tuning results in: ● Improved performance ● Reduced resource utilization This may lead to: ● Improved stability and predictability ● Smaller hardware footprint Not uncommon to observe efficiency improvements $gte 99%
Acme Games Introduces: ShortFite Tales of a release pre-launch testing
Getting Ready for Launch! MongoDB Certified Developers and DBA Stakeholders
Become a MongoDB Certified Developer / DBA
11 Stakeholder Concerns ● Game nearly complete ● Team starts to do capacity testing
12 Stakeholder Concerns ● Game nearly complete ● Team starts to do capacity testing Indexes support the efficient execution of queries in MongoDB
13 Stakeholder Concerns ● Game nearly complete ● Team starts to do capacity testing Ace Sue … … Bob Indexes support the efficient execution of queries in MongoDB
Stakeholder #1 Concern App being stress tested Concerns over current performance
Stakeholder #1 Concern Developers created index db.games.createIndex({ gamerTag: 1 }) This query takes several seconds to execute: db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) Adding the index on score does not help! db.games.createIndex({ score: -1 })
Stakeholder #1 Concern Developers created index db.games.createIndex({ gamerTag: 1 }) This query takes several seconds to execute: db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) Adding the index on score does not help! db.games.createIndex({ score: -1 }) “Clearly MongoDB is not webscale!”
Blocking Operations
18 Blocking Operation Formally: “An operation which must process all input before it can begin to produce any output.” Opposite of the often desirable “fully pipelined” plan which can stream results back as soon as they are found. Commonly observed when a sort is added to a query
Sorting with blocking
Sorting with blocking
Sorting with blocking
Sorting with blocking
Sorting with blocking
Sorting with blocking
Sorting with blocking
Sorting with blocking
Indexes that support sorting db.games.createIndex({ gamerTag: 1, score:-1})
Sorting without blocking
Sorting without blocking
Sorting without blocking
Sorting without blocking
32 Blocking Stages $sort In aggregation and find $group $bucket $count $facet Are there any other blocking operations?
Aggregation Pipeline Stages?
34 Working with blocking stages For sorting: Add a supporting index Worth the overhead in almost all circumstances For other stages: Do you need the blocking stage? Offload to secondary member
Stakeholder #1 Concern Performance of db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) “Clearly MongoDB is not webscale!”
Stakeholder #1 Concern Performance of db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) db.games.createIndex({ gamerTag: 1, score:-1 })
Stakeholder #1 Concern Performance of db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) db.games.createIndex({ gamerTag: 1, score:-1 }) "That’ll work great!”
Stakeholder #2 Concern The $and version of a query returns quickly: db.games.find({ $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) But the $or version is slow: db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Concern #2: Multiple queries same index The $and version of a query returns quickly: db.games.find({ $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) But the $or version is slow: db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) We just created an index with both those fields… Can it be used?
$or
$and example Query on games: db.games.find({ $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) Matching games: { gamerTag: "Ace", score: 9500 } Non-matching games: { gamerTag: "Ace", score: 500 }, { gamerTag: "Bob", score: 9500 }, { gamerTag: "Bob", score: 500 }
Groups of documents score: {$gt: 9000}gamerTag: "Ace" { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 }
gamerTag: "Ace" $and Venn Diagram (logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
$and Venn Diagram (logical) { gamerTag: "Bob", score: 500 } gamerTag: "Ace" { gamerTag: "Ace", score: 500 } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
gamerTag: "Ace" $and Venn Diagram (logical) score: {$gt: 9000} db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
gamerTag: "Ace" $and Venn Diagram (logical) score: {$gt: 9000} db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
$and Venn Diagram (logical) score: {$gt: 9000}gamerTag: "Ace" db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
$and Venn Diagram (logical) score: {$gt: 9000}gamerTag: "Ace" db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
$and Venn Diagram (logical) score: {$gt: 9000}gamerTag: "Ace" db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
Ace Bob {gamerTag:1 , score:-1} 500 9500 500 9500 $and Index Visualization db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
Ace Bob {gamerTag:1 , score:-1} 500 9500 500 9500 "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } $and Index Visualization db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
Ace Bob 500 9500 500 9500 {gamerTag:1 , score:-1} $and Index Visualization "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
Bob 500 9500 500 9500 Ace {gamerTag:1 , score:-1} $and Index Visualization "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
$and Index Visualization Bob 500 500 9500 Ace 9500 {gamerTag:1 , score:-1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
$or example Query on games: db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) Matching games: { gamerTag: "Ace", score: 9500 }, { gamerTag: "Ace", score: 500 }, { gamerTag: "Bob", score: 9500 } Non-matching games: { gamerTag: "Bob", score: 500 }
gamerTag: "Ace" $or Venn Diagram (logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or Venn Diagram (logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or Venn Diagram (logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
score: {$gt: 9000} $or Venn Diagram (logical) { gamerTag: "Ace", score: 9500 } { gamerTag: "Bob", score: 500 } { gamerTag: "Ace", score: 500 } gamerTag: "Ace" { gamerTag: "Bob", score: 9500 } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
score: {$gt: 9000}gamerTag: "Ace" $or Venn Diagram (logical) { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
score: {$gt: 9000}gamerTag: "Ace" $or Venn Diagram (logical) db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
score: {$gt: 9000}gamerTag: "Ace" $or Venn Diagram (logical) db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
score: {$gt: 9000}gamerTag: "Ace" $or Venn Diagram (logical) db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
score: {$gt: 9000} $or Venn Diagram (logical) gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
score: {$gt: 9000} $or Venn Diagram (logical) gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (single) Index visualization Ace Bob {gamerTag:1 , score:-1} 500 9500 500 9500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Ace Bob {gamerTag:1 , score:-1} 500 9500 500 9500 Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} $or (single) Index visualization db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Ace Bob 500 9500 500 9500 {gamerTag:1 , score:-1} $or (single) Index visualization Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Bob 500 9500 500 9500 {gamerTag:1 , score:-1} Ace $or (single) Index visualization Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Bob 500 9500 {gamerTag:1 , score:-1} Ace 500 9500 $or (single) Index visualization Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
500 Bob 9500 $or (single) Index visualization {gamerTag:1 , score:-1} Ace 500 9500 Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
500 Bob 9500 Actual (Hinted) Index Bounds: "indexBounds" : { "gamerTag" : [ "[MinKey, MaxKey]" ], "score" : [ "[MaxKey, MinKey]" ]} $or (single) Index visualization {gamerTag:1 , score:-1} Ace 500 9500 Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Ace Bob 500 9500 500 9500 {gamerTag:1 , score:-1} So is there anything we can do to improve the performance of this query? $or (single) Index visualization Actual (Hinted) Index Bounds: "indexBounds" : { "gamerTag" : [ "[MinKey, MaxKey]" ], "score" : [ "[MaxKey, MinKey]" ]} Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Recommendations Use multiple indexes! db.data.createIndex({gamerTag: 1}) db.data.createIndex({score: 1})
$or (multiple) Index visualization Ace Bob {gamerTag:1 , score:-1} 500 9500 500 9500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace Bob db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace Bob {gamerTag:1} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace Bob {gamerTag:1} 500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace Bob {gamerTag:1} 500 9500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace Bob {gamerTag:1} 500 9500 {score:1} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace Bob {gamerTag:1} 500 9500 {score:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Ace Bob 500 9500 {score:1}{gamerTag:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Bob 500 9500 {score:1} Ace {gamerTag:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Bob 500 9500 {score:1}{gamerTag:1} Ace "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Bob 500 9500 {gamerTag:1} Ace {score:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
$or (multiple) Index visualization Bob 500 9500 {gamerTag:1} Ace {score:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
Recommendations Use multiple indexes! db.data.createIndex({gamerTag: 1}) db.data.createIndex({score: 1})
Recommendations Use multiple indexes! db.data.createIndex({gamerTag: 1}) db.data.createIndex({score: 1}) We already have the {gamerTag:1, score:-1} index, do we need both of these new ones?
Recommendations Use multiple indexes! db.data.createIndex({gamerTag: 1}) db.data.createIndex({score: 1}) db.games.createIndex({ gamerTag: 1, score:-1 })
Stakeholder #2 Concern db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) Having the right index is critical "Super!!”
93 Stakeholder #3 Concern “Wait wait wait, we can’t even FIND the gamers!” A basic search on gamerTag takes several seconds already: db.games.find({gamerTag: /^Ace$/i}) “This query is SLOWER with the index than it is without it!”
Case Insensitive Queries
Matching games: { gamerTag: "Ace", score: 9500 } Non-matching games: { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 }, { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 } db.games.find({ gamerTag: /^Ace$/ }) //equivalent to db.games.find({ gamerTag: “Ace” }) Case Sensitive
Case Sensitive ace aCe acxyz Ace Ace mdb ACE Bob "indexBounds" : { "gamerTag" : [ "["Ace", "Acf")", "[/^Ace$/, /^Ace$/]" ]} Matching games: { gamerTag: "Ace", score: 9500 } Non-matching games: { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 }, { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 }
Matching games: { gamerTag: "Ace", score: 9500 }, { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 } Non-matching games: { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 } Case Insensitive db.games.find({ gamerTag: /^Ace$/i }) //equivalent to db.games.find({ gamerTag: { $regex: “^Ace$”, $options: “i” } }) //equivalent to db.games.find({ gamerTag: “Ace”}) .collation({locale:’en’,strength:2})
Case Insensitive db.games.find({ gamerTag: /^Ace$/i }) //equivalent to db.games.find({ gamerTag: { $regex: “^Ace$”, $options: “i” } }) //equivalent to db.games.find({ gamerTag: “Ace”}) .collation({locale:’en’, strength:2}) Would a $text search be the same as well? Matching games: { gamerTag: "Ace", score: 9500 }, { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 } Non-matching games: { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 }
Case Insensitive ace aCe acxyz Ace Ace mdb ACE Bob "indexBounds" : { "gamerTag" : [ “["", {})", "[/^Ace$/i, /^Ace$/i]" ] } Matching games: { gamerTag: "Ace", score: 9500 }, { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 } Non-matching games: { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 }
100 Recommendations Case insensitive index! Collations available since 3.4
101 Recommendations Case insensitive index! Collations available since 3.4 db.games.createIndex( { gamerTag: 1}, { collation: { locale: 'en', strength: 2 } } ) Store a transformed (eg toLower()) copy of the string
102 https://docs.mongodb.com/manual/reference/collation/index.html Collations
103 Stakeholder #3 Concern db.games.find({gamerTag: “Ace”}) .collation({locale:'en', strength:2})
104 Stakeholder #3 Concern db.games.find({gamerTag: “Ace”}) .collation({locale:'en', strength:2})
105 Stakeholder #3 Concern db.games.find({gamerTag: “Ace”}) .collation({locale:'en', strength:2}) “Wow, MongoDB can do anything!!!!1!”
Summary
107 Work Smarter Not Harder Understand the business logic Index appropriately Is it the right index to support the query? Be aware of: Blocking Stages Usage of $or Case sensitivity Leverage the Performance Advisor
108 Work Smarter Not Harder Understand the business logic Index appropriately Is it the right index to support the query? Be aware of: Blocking Stages Usage of $or Case sensitivity Leverage the Performance Advisor
109 Countdown to ShortFite Powered by an optimized MongoDB environment, ShortFite is sure to be a hit!
110 The Power of Query Optimization Query tuning results in: ● Improved performance ● Reduced resource utilization This may lead to: ● Improved stability and predictability ● A smaller hardware footprint Not uncommon to observe efficiency improvements $gte 99%
111 Become the HERO! - Plenty of resources out there - http://university.mongodb.com - http://docs.mongodb.com
Norberto Leite Lead Engineer norberto@mongodb.com @nleite
Avoid Query Pitfalls

More Related Content

PPTX
MongoDB.local Seattle 2019: Tips & Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
PDF
Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
PDF
Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
PDF
MongoDB.local Austin 2018: Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
PPTX
MongoDB.local Sydney 2019: Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
PDF
MongoDB World 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
PDF
MongoDB World 2019: How to Keep an Average API Response Time Less than 5ms wi...
byMongoDB
 
PPT
Graph Databases
byJosh Adell
 
MongoDB.local Seattle 2019: Tips & Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
MongoDB.local Austin 2018: Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
MongoDB.local Sydney 2019: Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
MongoDB World 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
MongoDB World 2019: How to Keep an Average API Response Time Less than 5ms wi...
byMongoDB
 
Graph Databases
byJosh Adell
 

What's hot

PDF
Ecuacionesfuncionales2 1
byAngélica Quiero Todavía
 
PDF
Ecuacionesfuncionales1 1
byAngélica Quiero Todavía
 
KEY
Potential Friend Finder
byRichard Schneeman
 
PDF
Gareth hayes. non alphanumeric javascript-php and shared fuzzing
byYury Chemerkin
 
PDF
Aggregation Pipeline Power++: MongoDB 4.2 파이프 라인 쿼리, 업데이트 및 구체화된 뷰 소개 [MongoDB]
byMongoDB
 
PDF
Introduction to Search Systems - ScaleConf Colombia 2017
byToria Gibbs
 
PDF
WordCamp Bristol 2019 - WordPress custom theme building
byJonny Allbut
 
PDF
How Search Engines Work (A Thing I Didn't Learn in University)
byToria Gibbs
 
PDF
Why there is 15% unnecesary co2 emissions in road transport
byCarl-Ivar Ahlqvist
 
PDF
A Search Index is Not a Database Index - Full Stack Toronto 2017
byToria Gibbs
 
PPT
MySQLConf2009: Taking ActiveRecord to the Next Level
byBlythe Dunham
 
DOCX
New tags in html5
bySathyaseelanK1
 
PDF
New opportunities for connected data
byNeo4j
 
PDF
Proposed PHP function: is_literal()
byCraig Francis
 
PPTX
Reading the .explain() Output
byMongoDB
 
PDF
CouchDB @ red dirt ruby conference
byleinweber
 
PPT
Heroku Waza 2013 Lessons Learned
bySimon Bagreev
 
PDF
A17 indexing and query optimization by paul pederson
byInsight Technology, Inc.
 
PDF
The Ring programming language version 1.5.4 book - Part 51 of 185
byMahmoud Samir Fayed
 
Ecuacionesfuncionales2 1
byAngélica Quiero Todavía
 
Ecuacionesfuncionales1 1
byAngélica Quiero Todavía
 
Potential Friend Finder
byRichard Schneeman
 
Gareth hayes. non alphanumeric javascript-php and shared fuzzing
byYury Chemerkin
 
Aggregation Pipeline Power++: MongoDB 4.2 파이프 라인 쿼리, 업데이트 및 구체화된 뷰 소개 [MongoDB]
byMongoDB
 
Introduction to Search Systems - ScaleConf Colombia 2017
byToria Gibbs
 
WordCamp Bristol 2019 - WordPress custom theme building
byJonny Allbut
 
How Search Engines Work (A Thing I Didn't Learn in University)
byToria Gibbs
 
Why there is 15% unnecesary co2 emissions in road transport
byCarl-Ivar Ahlqvist
 
A Search Index is Not a Database Index - Full Stack Toronto 2017
byToria Gibbs
 
MySQLConf2009: Taking ActiveRecord to the Next Level
byBlythe Dunham
 
New tags in html5
bySathyaseelanK1
 
New opportunities for connected data
byNeo4j
 
Proposed PHP function: is_literal()
byCraig Francis
 
Reading the .explain() Output
byMongoDB
 
CouchDB @ red dirt ruby conference
byleinweber
 
Heroku Waza 2013 Lessons Learned
bySimon Bagreev
 
A17 indexing and query optimization by paul pederson
byInsight Technology, Inc.
 
The Ring programming language version 1.5.4 book - Part 51 of 185
byMahmoud Samir Fayed
 

Similar to Avoid Query Pitfalls

PPTX
Tips and Tricks for Avoiding Common Query Pitfalls Christian Kurze
byMongoDB
 
PDF
MongoDB World 2018: Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
PDF
MongoDB .local London 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
PDF
MongoDB .local London 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byLisa Roth, PMP
 
PDF
MongoDB .local Bengaluru 2019: Tips and Tricks++ for Querying and Indexing Mo...
byMongoDB
 
PDF
MongoDB .local Houston 2019:Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
PDF
MongoDB .local San Francisco 2020: Tips and Tricks++ for Querying and Indexin...
byMongoDB
 
PDF
MongoDB .local Munich 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
PDF
MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing
byMongoDB
 
PDF
MongoDB World 2016: Deciphering .explain() Output
byMongoDB
 
PDF
MongoDB Performance Tuning
byMongoDB
 
KEY
MongoDB - Introduction
byVagmi Mudumbai
 
PPTX
Query Optimization in MongoDB
byHamoon Mohammadian Pour
 
KEY
Thoughts on MongoDB Analytics
byrogerbodamer
 
PPTX
MongoDB Aggregations Indexing and Profiling
byManish Kapoor
 
PDF
MongoD Essentials
byzahid-mian
 
PPT
Fast querying indexing for performance (4)
byMongoDB
 
PDF
Map/reduce, geospatial indexing, and other cool features (Kristina Chodorow)
byMongoSF
 
PPTX
Mongo Crud for beginners and learning .pptx
bysivasanniboina1
 
PDF
Latinoware
bykchodorow
 
Tips and Tricks for Avoiding Common Query Pitfalls Christian Kurze
byMongoDB
 
MongoDB World 2018: Tips and Tricks for Avoiding Common Query Pitfalls
byMongoDB
 
MongoDB .local London 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
MongoDB .local London 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byLisa Roth, PMP
 
MongoDB .local Bengaluru 2019: Tips and Tricks++ for Querying and Indexing Mo...
byMongoDB
 
MongoDB .local Houston 2019:Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
MongoDB .local San Francisco 2020: Tips and Tricks++ for Querying and Indexin...
byMongoDB
 
MongoDB .local Munich 2019: Tips and Tricks++ for Querying and Indexing MongoDB
byMongoDB
 
MongoDB .local Toronto 2019: Tips and Tricks for Effective Indexing
byMongoDB
 
MongoDB World 2016: Deciphering .explain() Output
byMongoDB
 
MongoDB Performance Tuning
byMongoDB
 
MongoDB - Introduction
byVagmi Mudumbai
 
Query Optimization in MongoDB
byHamoon Mohammadian Pour
 
Thoughts on MongoDB Analytics
byrogerbodamer
 
MongoDB Aggregations Indexing and Profiling
byManish Kapoor
 
MongoD Essentials
byzahid-mian
 
Fast querying indexing for performance (4)
byMongoDB
 
Map/reduce, geospatial indexing, and other cool features (Kristina Chodorow)
byMongoSF
 
Mongo Crud for beginners and learning .pptx
bysivasanniboina1
 
Latinoware
bykchodorow
 

More from Norberto Leite

PDF
Data Modelling for MongoDB - MongoDB.local Tel Aviv
byNorberto Leite
 
PPTX
MongoDB and Spark
byNorberto Leite
 
PDF
Mongo db 3.4 Overview
byNorberto Leite
 
PDF
MongoDB Certification Study Group - May 2016
byNorberto Leite
 
PDF
Geospatial and MongoDB
byNorberto Leite
 
PDF
MongodB Internals
byNorberto Leite
 
PDF
MongoDB WiredTiger Internals
byNorberto Leite
 
PDF
MongoDB 3.2 Feature Preview
byNorberto Leite
 
PDF
Mongodb Spring
byNorberto Leite
 
PDF
MongoDB on Azure
byNorberto Leite
 
PDF
MongoDB: Agile Combustion Engine
byNorberto Leite
 
PDF
MongoDB Capacity Planning
byNorberto Leite
 
PDF
Spark and MongoDB
byNorberto Leite
 
PDF
Analyse Yourself
byNorberto Leite
 
PDF
Python and MongoDB
byNorberto Leite
 
PDF
Strongly Typed Languages and Flexible Schemas
byNorberto Leite
 
PDF
Effectively Deploying MongoDB on AEM
byNorberto Leite
 
PPTX
Advanced applications with MongoDB
byNorberto Leite
 
PDF
MongoDB and Node.js
byNorberto Leite
 
PPTX
MongoDB + Spring
byNorberto Leite
 
Data Modelling for MongoDB - MongoDB.local Tel Aviv
byNorberto Leite
 
MongoDB and Spark
byNorberto Leite
 
Mongo db 3.4 Overview
byNorberto Leite
 
MongoDB Certification Study Group - May 2016
byNorberto Leite
 
Geospatial and MongoDB
byNorberto Leite
 
MongodB Internals
byNorberto Leite
 
MongoDB WiredTiger Internals
byNorberto Leite
 
MongoDB 3.2 Feature Preview
byNorberto Leite
 
Mongodb Spring
byNorberto Leite
 
MongoDB on Azure
byNorberto Leite
 
MongoDB: Agile Combustion Engine
byNorberto Leite
 
MongoDB Capacity Planning
byNorberto Leite
 
Spark and MongoDB
byNorberto Leite
 
Analyse Yourself
byNorberto Leite
 
Python and MongoDB
byNorberto Leite
 
Strongly Typed Languages and Flexible Schemas
byNorberto Leite
 
Effectively Deploying MongoDB on AEM
byNorberto Leite
 
Advanced applications with MongoDB
byNorberto Leite
 
MongoDB and Node.js
byNorberto Leite
 
MongoDB + Spring
byNorberto Leite
 

Recently uploaded

PPTX
Zotero Software Demonstration. Biomedical Perspective
byDr Snehashis Singha
 
PDF
DSD-INT 2025 Climate and impact attribution of compound flooding induced by t...
byDeltares
 
PDF
DSD-INT 2025 DevOps - Automated testing and delivery of Delft3D FM - van West...
byDeltares
 
PDF
DSD-INT 2025 Modernizing Hydrodynamics in Large Flood Forecasting System - Mi...
byDeltares
 
PDF
Testing in Terraform: only for platform nerds?
byQAware GmbH
 
PDF
DSD-INT 2025 Building-Aware Flood and Lifeline Scour Modeling with Delft3D FM...
byDeltares
 
PDF
DSD-INT 2025 Timor-Leste Flood exposure and Climate Change assessment - Ogunwumi
byDeltares
 
PDF
7 Levels of a Web Performance Journey @ Google DevFest Brooklyn 2025
bySergeyChernyshev
 
PPTX
Building a RAG System for Customer Support - L1
byBogdan Mustata
 
PDF
DSD-INT 2025 Delft3D FM Suite 2026.01 2D3D - New features + Improvements - Sp...
byDeltares
 
PDF
Microservices Architecture Benefits For Mobile Development.pdf
bydavidjohansen1597
 
PDF
DSD-INT 2025 Modelling Non-Newtonian Slurry Beaching with Delft3D-Slurry - Bi
byDeltares
 
PPTX
Understanding-ROM-RAM-Cache-and-Buffer-Memory.pptx.pptx
bylata2850
 
PDF
Bring AI and build AI agents into your Jakarta EE Apps with LangChain4J-CDI
byBuhake Sindi
 
PPTX
CRM Development Company | Custom CRM Development Services
byyugababu033
 
PDF
DSD-INT 2025 Coupling SFINCS to a flood risk model to evaluate the effects of...
byDeltares
 
PDF
DSD-INT 2025 Advancing Urban Flood Modeling with Delft3D FM 1D2D - A Pilot St...
byDeltares
 
PDF
DSD-INT 2025 MeshKernel and D-Grid Editor - Stout
byDeltares
 
PPTX
Integration: Why Is It So Difficult? Lessons from Vipps’ API Journey
byChristian Løverås
 
PPTX
wAIred_LearnwithoutAI_KotlinDevDay_27112025.pptx
bySimonedeGijt
 
Zotero Software Demonstration. Biomedical Perspective
byDr Snehashis Singha
 
DSD-INT 2025 Climate and impact attribution of compound flooding induced by t...
byDeltares
 
DSD-INT 2025 DevOps - Automated testing and delivery of Delft3D FM - van West...
byDeltares
 
DSD-INT 2025 Modernizing Hydrodynamics in Large Flood Forecasting System - Mi...
byDeltares
 
Testing in Terraform: only for platform nerds?
byQAware GmbH
 
DSD-INT 2025 Building-Aware Flood and Lifeline Scour Modeling with Delft3D FM...
byDeltares
 
DSD-INT 2025 Timor-Leste Flood exposure and Climate Change assessment - Ogunwumi
byDeltares
 
7 Levels of a Web Performance Journey @ Google DevFest Brooklyn 2025
bySergeyChernyshev
 
Building a RAG System for Customer Support - L1
byBogdan Mustata
 
DSD-INT 2025 Delft3D FM Suite 2026.01 2D3D - New features + Improvements - Sp...
byDeltares
 
Microservices Architecture Benefits For Mobile Development.pdf
bydavidjohansen1597
 
DSD-INT 2025 Modelling Non-Newtonian Slurry Beaching with Delft3D-Slurry - Bi
byDeltares
 
Understanding-ROM-RAM-Cache-and-Buffer-Memory.pptx.pptx
bylata2850
 
Bring AI and build AI agents into your Jakarta EE Apps with LangChain4J-CDI
byBuhake Sindi
 
CRM Development Company | Custom CRM Development Services
byyugababu033
 
DSD-INT 2025 Coupling SFINCS to a flood risk model to evaluate the effects of...
byDeltares
 
DSD-INT 2025 Advancing Urban Flood Modeling with Delft3D FM 1D2D - A Pilot St...
byDeltares
 
DSD-INT 2025 MeshKernel and D-Grid Editor - Stout
byDeltares
 
Integration: Why Is It So Difficult? Lessons from Vipps’ API Journey
byChristian Løverås
 
wAIred_LearnwithoutAI_KotlinDevDay_27112025.pptx
bySimonedeGijt
 

Avoid Query Pitfalls

  • 1.
  • 2.
    2 Agenda Common Query Pitfalls Howto think about and how to solve them Tips and Tricks for Query Optimization
  • 3.
    3 Howdy! Disclaimer Who's this guy? NorbertoLeite Lead Engineer @nleite MongoDB
  • 4.
  • 5.
  • 6.
  • 7.
    7 The Power ofQuery Optimization Query tuning results in: ● Improved performance ● Reduced resource utilization This may lead to: ● Improved stability and predictability ● Smaller hardware footprint Not uncommon to observe efficiency improvements $gte 99%
  • 8.
    Acme Games Introduces: ShortFite Talesof a release pre-launch testing
  • 9.
  • 10.
  • 11.
    11 Stakeholder Concerns ● Game nearly complete ●Team starts to do capacity testing
  • 12.
    12 Stakeholder Concerns ● Game nearly complete ●Team starts to do capacity testing Indexes support the efficient execution of queries in MongoDB
  • 13.
    13 Stakeholder Concerns ● Game nearly complete ●Team starts to do capacity testing Ace Sue … … Bob Indexes support the efficient execution of queries in MongoDB
  • 14.
    Stakeholder #1 Concern Appbeing stress tested Concerns over current performance
  • 15.
    Stakeholder #1 Concern Developerscreated index db.games.createIndex({ gamerTag: 1 }) This query takes several seconds to execute: db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) Adding the index on score does not help! db.games.createIndex({ score: -1 })
  • 16.
    Stakeholder #1 Concern Developerscreated index db.games.createIndex({ gamerTag: 1 }) This query takes several seconds to execute: db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) Adding the index on score does not help! db.games.createIndex({ score: -1 }) “Clearly MongoDB is not webscale!”
  • 17.
  • 18.
    18 Blocking Operation Formally: “An operationwhich must process all input before it can begin to produce any output.” Opposite of the often desirable “fully pipelined” plan which can stream results back as soon as they are found. Commonly observed when a sort is added to a query
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
    Indexes that supportsorting db.games.createIndex({ gamerTag: 1, score:-1})
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
    32 Blocking Stages $sort In aggregation andfind $group $bucket $count $facet Are there any other blocking operations?
  • 33.
  • 34.
    34 Working with blockingstages For sorting: Add a supporting index Worth the overhead in almost all circumstances For other stages: Do you need the blocking stage? Offload to secondary member
  • 35.
    Stakeholder #1 Concern Performanceof db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) “Clearly MongoDB is not webscale!”
  • 36.
    Stakeholder #1 Concern Performanceof db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) db.games.createIndex({ gamerTag: 1, score:-1 })
  • 37.
    Stakeholder #1 Concern Performanceof db.games.find( { gamerTag: "Ace" } ).sort({score:-1}) db.games.createIndex({ gamerTag: 1, score:-1 }) "That’ll work great!”
  • 38.
    Stakeholder #2 Concern The$and version of a query returns quickly: db.games.find({ $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) But the $or version is slow: db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 39.
    Concern #2: Multiplequeries same index The $and version of a query returns quickly: db.games.find({ $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) But the $or version is slow: db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) We just created an index with both those fields… Can it be used?
  • 40.
  • 41.
    $and example Query ongames: db.games.find({ $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) Matching games: { gamerTag: "Ace", score: 9500 } Non-matching games: { gamerTag: "Ace", score: 500 }, { gamerTag: "Bob", score: 9500 }, { gamerTag: "Bob", score: 500 }
  • 42.
    Groups of documents score:{$gt: 9000}gamerTag: "Ace" { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 }
  • 43.
    gamerTag: "Ace" $and VennDiagram (logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 44.
    $and Venn Diagram(logical) { gamerTag: "Bob", score: 500 } gamerTag: "Ace" { gamerTag: "Ace", score: 500 } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 45.
    gamerTag: "Ace" $and VennDiagram (logical) score: {$gt: 9000} db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 46.
    gamerTag: "Ace" $and VennDiagram (logical) score: {$gt: 9000} db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 47.
    $and Venn Diagram(logical) score: {$gt: 9000}gamerTag: "Ace" db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 48.
    $and Venn Diagram(logical) score: {$gt: 9000}gamerTag: "Ace" db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 49.
    $and Venn Diagram(logical) score: {$gt: 9000}gamerTag: "Ace" db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 50.
    Ace Bob {gamerTag:1 , score:-1} 5009500 500 9500 $and Index Visualization db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 51.
    Ace Bob {gamerTag:1 , score:-1} 5009500 500 9500 "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } $and Index Visualization db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 52.
    Ace Bob 500 9500 5009500 {gamerTag:1 , score:-1} $and Index Visualization "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 53.
    Bob 500 9500 500 9500 Ace {gamerTag:1 ,score:-1} $and Index Visualization "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 54.
    $and Index Visualization Bob 500 5009500 Ace 9500 {gamerTag:1 , score:-1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000.0)" ] } db.games.find( { $and : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ]})
  • 55.
    $or example Query ongames: db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) Matching games: { gamerTag: "Ace", score: 9500 }, { gamerTag: "Ace", score: 500 }, { gamerTag: "Bob", score: 9500 } Non-matching games: { gamerTag: "Bob", score: 500 }
  • 56.
    gamerTag: "Ace" $or VennDiagram (logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 57.
    $or Venn Diagram(logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 58.
    $or Venn Diagram(logical) score: {$gt: 9000} { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 59.
    score: {$gt: 9000} $orVenn Diagram (logical) { gamerTag: "Ace", score: 9500 } { gamerTag: "Bob", score: 500 } { gamerTag: "Ace", score: 500 } gamerTag: "Ace" { gamerTag: "Bob", score: 9500 } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 60.
    score: {$gt: 9000}gamerTag:"Ace" $or Venn Diagram (logical) { gamerTag: "Ace", score: 9500 } { gamerTag: "Ace", score: 500 } { gamerTag: "Bob", score: 9500 } { gamerTag: "Bob", score: 500 } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 61.
    score: {$gt: 9000}gamerTag:"Ace" $or Venn Diagram (logical) db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 62.
    score: {$gt: 9000}gamerTag:"Ace" $or Venn Diagram (logical) db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 63.
    score: {$gt: 9000}gamerTag:"Ace" $or Venn Diagram (logical) db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 64.
    score: {$gt: 9000} $orVenn Diagram (logical) gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 65.
    score: {$gt: 9000} $orVenn Diagram (logical) gamerTag: "Ace" db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 66.
    $or (single) Indexvisualization Ace Bob {gamerTag:1 , score:-1} 500 9500 500 9500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 67.
    Ace Bob {gamerTag:1 , score:-1} 5009500 500 9500 Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} $or (single) Index visualization db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 68.
    Ace Bob 500 9500 5009500 {gamerTag:1 , score:-1} $or (single) Index visualization Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 69.
    Bob 500 9500 500 9500 {gamerTag:1 ,score:-1} Ace $or (single) Index visualization Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 70.
    Bob 500 9500 {gamerTag:1 , score:-1} Ace 5009500 $or (single) Index visualization Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 71.
    500 Bob 9500 $or (single) Indexvisualization {gamerTag:1 , score:-1} Ace 500 9500 Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 72.
    500 Bob 9500 Actual (Hinted) IndexBounds: "indexBounds" : { "gamerTag" : [ "[MinKey, MaxKey]" ], "score" : [ "[MaxKey, MinKey]" ]} $or (single) Index visualization {gamerTag:1 , score:-1} Ace 500 9500 Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 73.
    Ace Bob 500 9500 5009500 {gamerTag:1 , score:-1} So is there anything we can do to improve the performance of this query? $or (single) Index visualization Actual (Hinted) Index Bounds: "indexBounds" : { "gamerTag" : [ "[MinKey, MaxKey]" ], "score" : [ "[MaxKey, MinKey]" ]} Expected Index Bounds: "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]" ], "score" : [ "[inf.0, 9000]" ]} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 74.
  • 75.
    $or (multiple) Indexvisualization Ace Bob {gamerTag:1 , score:-1} 500 9500 500 9500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 76.
    $or (multiple) Indexvisualization db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 77.
    $or (multiple) Indexvisualization Ace db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 78.
    $or (multiple) Indexvisualization Ace Bob db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 79.
    $or (multiple) Indexvisualization Ace Bob {gamerTag:1} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 80.
    $or (multiple) Indexvisualization Ace Bob {gamerTag:1} 500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 81.
    $or (multiple) Indexvisualization Ace Bob {gamerTag:1} 500 9500 db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 82.
    $or (multiple) Indexvisualization Ace Bob {gamerTag:1} 500 9500 {score:1} db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 83.
    $or (multiple) Indexvisualization Ace Bob {gamerTag:1} 500 9500 {score:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 84.
    $or (multiple) Indexvisualization Ace Bob 500 9500 {score:1}{gamerTag:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 85.
    $or (multiple) Indexvisualization Bob 500 9500 {score:1} Ace {gamerTag:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 86.
    $or (multiple) Indexvisualization Bob 500 9500 {score:1}{gamerTag:1} Ace "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 87.
    $or (multiple) Indexvisualization Bob 500 9500 {gamerTag:1} Ace {score:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 88.
    $or (multiple) Indexvisualization Bob 500 9500 {gamerTag:1} Ace {score:1} "indexBounds" : { "gamerTag" : [ "["Ace", "Ace"]” ] } "indexBounds" : { "score" : [ "(9000.0, inf.0]” ] } db.games.find({ $or : [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] })
  • 89.
  • 90.
    Recommendations Use multiple indexes! db.data.createIndex({gamerTag:1}) db.data.createIndex({score: 1}) We already have the {gamerTag:1, score:-1} index, do we need both of these new ones?
  • 91.
    Recommendations Use multiple indexes! db.data.createIndex({gamerTag:1}) db.data.createIndex({score: 1}) db.games.createIndex({ gamerTag: 1, score:-1 })
  • 92.
    Stakeholder #2 Concern db.games.find({ $or: [ { gamerTag: "Ace" }, { score: {$gt: 9000} } ] }) Having the right index is critical "Super!!”
  • 93.
    93 Stakeholder #3 Concern “Waitwait wait, we can’t even FIND the gamers!” A basic search on gamerTag takes several seconds already: db.games.find({gamerTag: /^Ace$/i}) “This query is SLOWER with the index than it is without it!”
  • 94.
  • 95.
    Matching games: { gamerTag:"Ace", score: 9500 } Non-matching games: { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 }, { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 } db.games.find({ gamerTag: /^Ace$/ }) //equivalent to db.games.find({ gamerTag: “Ace” }) Case Sensitive
  • 96.
    Case Sensitive ace aCeacxyz Ace Ace mdb ACE Bob "indexBounds" : { "gamerTag" : [ "["Ace", "Acf")", "[/^Ace$/, /^Ace$/]" ]} Matching games: { gamerTag: "Ace", score: 9500 } Non-matching games: { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 }, { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 }
  • 97.
    Matching games: { gamerTag:"Ace", score: 9500 }, { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 } Non-matching games: { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 } Case Insensitive db.games.find({ gamerTag: /^Ace$/i }) //equivalent to db.games.find({ gamerTag: { $regex: “^Ace$”, $options: “i” } }) //equivalent to db.games.find({ gamerTag: “Ace”}) .collation({locale:’en’,strength:2})
  • 98.
    Case Insensitive db.games.find({ gamerTag: /^Ace$/i }) //equivalentto db.games.find({ gamerTag: { $regex: “^Ace$”, $options: “i” } }) //equivalent to db.games.find({ gamerTag: “Ace”}) .collation({locale:’en’, strength:2}) Would a $text search be the same as well? Matching games: { gamerTag: "Ace", score: 9500 }, { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 } Non-matching games: { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 }
  • 99.
    Case Insensitive ace aCeacxyz Ace Ace mdb ACE Bob "indexBounds" : { "gamerTag" : [ “["", {})", "[/^Ace$/i, /^Ace$/i]" ] } Matching games: { gamerTag: "Ace", score: 9500 }, { gamerTag: "ACE", score: 500 }, { gamerTag: "aCe", score: 9500 }, { gamerTag: "ace", score: 0 } Non-matching games: { gamerTag: "Bob", score: 500 }, { gamerTag: "acxyz", score: 9500 }, { gamerTag: "Ace mdb", score: 9500 }
  • 100.
  • 101.
    101 Recommendations Case insensitive index! Collationsavailable since 3.4 db.games.createIndex( { gamerTag: 1}, { collation: { locale: 'en', strength: 2 } } ) Store a transformed (eg toLower()) copy of the string
  • 102.
  • 103.
    103 Stakeholder #3 Concern db.games.find({gamerTag:“Ace”}) .collation({locale:'en', strength:2})
  • 104.
    104 Stakeholder #3 Concern db.games.find({gamerTag:“Ace”}) .collation({locale:'en', strength:2})
  • 105.
    105 Stakeholder #3 Concern db.games.find({gamerTag:“Ace”}) .collation({locale:'en', strength:2}) “Wow, MongoDB can do anything!!!!1!”
  • 106.
  • 107.
    107 Work Smarter NotHarder Understand the business logic Index appropriately Is it the right index to support the query? Be aware of: Blocking Stages Usage of $or Case sensitivity Leverage the Performance Advisor
  • 108.
    108 Work Smarter NotHarder Understand the business logic Index appropriately Is it the right index to support the query? Be aware of: Blocking Stages Usage of $or Case sensitivity Leverage the Performance Advisor
  • 109.
    109 Countdown to ShortFite Poweredby an optimized MongoDB environment, ShortFite is sure to be a hit!
  • 110.
    110 The Power ofQuery Optimization Query tuning results in: ● Improved performance ● Reduced resource utilization This may lead to: ● Improved stability and predictability ● A smaller hardware footprint Not uncommon to observe efficiency improvements $gte 99%
  • 111.
    111 Become the HERO! -Plenty of resources out there - http://university.mongodb.com - http://docs.mongodb.com
  • 112.