Common Query Operators

1 . Common Query Operators Overview about how to use Query Operators Adamo Tonete Senior MongoDB Support Engineer Webinar 2017-03-02 1 © 2017 Percona 

2 .Who am I? Adamo Tonete ▪ Senior MongoDB Support Engineer • ~ 4 years working with MongoDB 1.5 years at percona ▪ ~ 8 years working with databases ▪ Located in São Paulo, Brazil 2 © 2017 Percona 

3 .Agenda ▪ What is MongoDB ▪ Query Operators ▪ Document Example ▪ Combinating Operators ▪ Index Example ▪ Covered Index ▪ Query Optimizer ▪ Questions ▪ Explain 3 © 2017 Percona 

4 .What is MongoDB? Document Oriented Database ▪ NoSQL technology ▪ Fast ▪ Open Source ▪ Easy to scale out ▪ Database made to new technologies 4 © 2017 Percona 

5 .The MongoDB Document { _id : 1, name : 'Joseph, last: 'Lastname', like : ['movies','reading',1,false], addr : { strt : 'street 15th', city: 'nowhere', nr: 230 } } 5 © 2017 Percona 

6 .Relational Database _id name lastname _id like 1 Joseph Lastname 1 movies _id street city 1 reading 1 15th street nowere ▪ Select _id, name, age, like, street, city from people Inner join likes on likes._id = people._id Inner join address on address._id = people._id 6 © 2017 Percona 

7 .Indexes MongoDB supports indexes with balanced tree (B-tree) algorithm. ▪ Index only saves the values not the names • we can seek backwards or forward in a index ▪ Why indexing subdocuments can be dangerous? 7 © 2017 Percona 

8 .Subdocuments { _id : ObjectId('1234987634758374593302'), address : { number : 1, street : 'mystree 2sd' } } db.foo.ensureIndex({address : 1}) 8 © 2017 Percona 

9 .The MongoDB find command db.foo.find({name : 'Joseph'}) db.foo.find({'address.city' : 'New York'}) db.foo.find({$and : [{name : 'Joseph'}, {last : 'lastname'}]}) db.foo.find({name : 'Joseph', last : 'lastname'}) Same db.foo.find({$or : [{name : 'Joseph'}, {last : 'lastname'}]}) 9 © 2017 Percona 

10 .Query Optimizer Query ▪ Parallel execution, will chose the less effort query idx_1 idx_2 idx_3 idx_n 10 © 2017 Percona 

11 .How to read explain() db.foo.find({'address.number' : 1}).explain() { { _id : "queryPlanner" : { ObjectId('1234987634758374593302'), "plannerVersion" : 1, address : { number : 1, "namespace" : "webinar.foo", street : 'mystree 2sd' "indexFilterSet" : false, } "parsedQuery" : { } "address.number" : { "$eq" : 1 } }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "address.number" : { "$eq" : 1 11 © 2017 Percona 

12 .Common Query Operators ▪ $and ▪ $or ▪ $in ▪ $ne ▪ $exists ▪ $type 12 © 2017 Percona 

13 .Working with data db.foo.find() { "_id" : ObjectId("58ade1c87ac9496725f6b8b5"), "x" : 1 } { "_id" : ObjectId("58ade1ca7ac9496725f6b8b6"), "x" : 2 } { "_id" : ObjectId("58ade1cf7ac9496725f6b8b7"), "x" : 1, "y" : 1 } { "_id" : ObjectId("58ade1d17ac9496725f6b8b8"), "x" : 1, "y" : 2 } { "_id" : ObjectId("58ade1d57ac9496725f6b8b9"), "x" : 2, "y" : 1 } { "_id" : ObjectId("58ade1d97ac9496725f6b8ba"), "x" : 2, "y" : 2 } { "_id" : ObjectId("58ade2a57ac9496725f6b8bb"), "x" : 3, "w" : 1 } ▪ Indexes: • {x:1}, {y:1}, {x:1,y:1}, {y : 1, x : 1} 13 © 2017 Percona 

14 .Working with Data - Indexes db.foo.getIndexes() [ { { { "v" : 1, "v" : 1, "v" : 1, "key" : { "key" : { "key" : { "y" : 1 "y" : 1, "_id" : 1 }, "x" : 1 }, "name" : "y_1", }, "name" : "_id_", "ns" : "db1.foo" "name" : "y_1_x_1", "ns" : "db1.foo" }, "ns" : "db1.foo" }, { } { "v" : 1, ] "v" : 1, "key" : { "key" : { "x" : 1, "x" : 1 "y" : 1 }, }, "name" : "x_1", "name" : "x_1_y_1", "ns" : "db1.foo" "ns" : "db1.foo" }, }, 14 © 2017 Percona 

15 .$and db.foo.find({x : 1, y : 1}).explain() "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "y" : 1, "x" : 1 }, "indexName" : "y_1_x_1", "isMultiKey" : false, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 1, "direction" : "forward", "indexBounds" : { "y" : [ "[1.0, 1.0]" ], "x" : [ "[1.0, 1.0]" ] } } }, 15 © 2017 Percona 

16 .$or db.foo.find({$or : [{x : 1},{y : 1}]}).explain() "winningPlan" : { "stage" : "SUBPLAN", "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "OR", "inputStages" : [ { "stage" : "IXSCAN", "keyPattern" : { "x" : 1 }, "indexName" : "x_1", "isMultiKey" : false, "indexBounds" : { "x" : [ "[1.0, 1.0]" ] } }, { "stage" : "IXSCAN", "keyPattern" : { "y" : 1 }, "indexName" : "y_1", "indexBounds" : { "y" : [ "[1.0, 1.0]" ] } 16 } © 2017 Percona ] 

17 .$and + $or, what is the plan? db.foo.find({ $and : [{x : 2}, {$or : [{x : 1},{y : 1}]}]}) ▪ Filter x: 2 and then perform a $or comparison 17 © 2017 Percona 

18 .$exists db.foo.find({w : {$exists : true}}).explain() db.foo.find({w : {$exists : true}, x :1}).explain() { { "queryPlanner" : { "queryPlanner" : { "plannerVersion" : 1, "plannerVersion" : 1, "namespace" : "db1.foo", "namespace" : "db1.foo", "indexFilterSet" : false, "indexFilterSet" : false, "parsedQuery" : { "parsedQuery" : { "w" : { "$and" : [ "$exists" : true {"x" : { } "$eq" : 1}}, }, { "winningPlan" : { "w" : "$exists" : true}} "stage" : "COLLSCAN", ]}, "filter" : { "winningPlan" : { "w" : { "stage" : "FETCH", "$exists" : true "filter" : { } "w" : { }, "$exists" : true "direction" : "forward" } }, }, "rejectedPlans" : [ ] "inputStage" : { }, "stage" : "IXSCAN", "keyPattern" : { "x" : 1, "y" : 1 }, 18 © 2017 Percona 

19 .Querying Subdocuments db.foo.find({'address.number' : 1}).explain() { db.foo.ensureIndex({address : 1}) "queryPlanner" : { "plannerVersion" : 1, "namespace" : "webinar.foo", "indexFilterSet" : false, "parsedQuery" : { "address.number" : { "$eq" : 1 } }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "address.number" : { "$eq" : 1 } }, "direction" : "forward" }, "rejectedPlans" : [ ] 19 © 2017 Percona 

20 .Querying Subdocuments db.foo.find({'address' : {street : 1, number :1}}).explain() db.foo.ensureIndex({address : 1}) "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "address" : 1 }, "indexName" : "address_1", "isMultiKey" : false, "direction" : "forward", "indexBounds" : { "address" : [ "[{ street: 1.0, number: 1.0 }, { street: 1.0, number: 1.0 }]" ] } } 20 © 2017 Percona 

21 .Covered Index ▪ All the requested fields are in the index: db.foo.find({x : 1}, {x : 1, _id : 0}).explain() winningPlan : { … "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "y" : 1, "x" : 1 }, } 21 © 2017 Percona 

22 .Operators that may scan collection All commands can generate a collection scan if there is no index available but the following ones are the principals. ▪ $exists ▪ $ne plus non indexed field ▪ $type 22 © 2017 Percona 

23 .Quick review Avoid using low cardinality fields as indexed fields Use as many $eq and $and as possible. Try to write precise queries. Lots of $or commands can be bad. Use $exists in indexed fields, or at least combined with a high cardinality field. Indexes are not clusterized. Covered indexes are faster. 23 © 2017 Percona 

24 .Join us at Percona Live When: April 24-27, 2017 Where: Santa Clara, CA, USA The Percona Live Open Source Database Conference is a great event for users of any level using open source database technologies. • Get briefed on the hottest topics • Learn about building and maintaining high-performing deployments • Listen to technical experts and top industry leaders Use promo code “WebinarPL” to save an extra 15% off. Register now and get the advanced registration rate, but hurry prices go up March 5th https://www.percona.com/live/17/register Sponsorship opportunities available as well: https://www.percona.com/live/17/be-a-sponsor 

25 . Questions 25 © 2017 Percona 

26 . DATABASE PERFORMANCE Database Performance Matters MATTERS