Indexing with MongoDB Kirill Duborenko EPAM Systems 

Indexing with MongoDB ● Indexes overview ● Integrated queries profiling tool (explain) ● Indexes in examples 

Type of indexes ● Simple index (B-Tree) ● Primary key (_id index) ● Indexes on embedded fields ● Compound indexes ● Indexes on array fields 

Indexes modifiers ● Sparse {sparse: true} ● Unique {unique: true} ● Drop duplicates {unique: true, dropDups: true} ● Background {background:true} 

Index building in replica ● Primary node In background, for backgound indexes ● Secondary node In v2.1.0 – in background, for backgound indexes In v2.0.x and earlier – always in foreground ● Recovering node Always in foreground 

Indexing with MongoDB ● Indexes overview ● Integrated queries profiling tool (explain) ● Indexes in examples 

Overview > db.coll.find(query).explain() { "cursor" : "BasicCursor", "nscanned" : 1000, "nscannedObjects" : 1000, "n" : 145, "millis" : 0, … } 

Explain result (part 1) ● “cursor” - type of cursor (options: “BasicCursor” and “BtreeCursor”) ● “nscanned” - count of scanned items (for BasicCursor – documents, for BtreeCursor – index items) ● “nscannedObjects” - number of documments scanned ● “n” - count of matched documents 

Explain result (part 2) ● “nYields” - count of interrupts during query execution ● “millis” - time of query execution ● “scanAndOrder” - true, in case if in-memory sorting required ● “indexOnly” - covered index is used ● “isMultiKey” - index over array is used ● “clauses” - used for iteration over more than one index 

Index Bounds { "cursor" : "BtreeCursor a1_1 multi", ..., "indexBounds" : { "a1" : [ [7,8], [13,20] ] } } 

Indexing with MongoDB ● Indexes overview ● Integrated queries profiling tool (explain) ● Indexes in examples 

Iterating over two indexes db.coll.find({ $or:[ { v1:{ $gte:10, $lte:39 } }, { v3:{ $gte:35, $lte:49 } } ] }).explain() { "clauses" : [ { "cursor" : "BtreeCursor v1_1", "nscanned" : 30, "n" : 30, ... }, { "cursor" : "BtreeCursor v3_1", "nscanned" : 15, "n" : 10, ... } ] "nscanned" : 45, "nscannedObjects" : 45, "n" : 40, "millis" : 0 } 

$or operator and sorting db.coll.find({ $or:[ { v1:{ $gte:10, $lte:39 } }, { v3:{ $gte:35, $lte:49 } } ] }).sort({v2: 1}) .explain() { "cursor" : "BasicCursor", "nscanned" : 100, "nscannedObjects" : 100, "n" : 40, "scanAndOrder" : true, "millis" : 0, "nYields" : 0, "nChunkSkips" : 0, "isMultiKey" : false, "indexOnly" : false, "indexBounds" : { } } 

Compound indexes (Part 1) ● Query: db.coll.find( {v1:8,v2:8}).sort({v3:1}) ● Index: db.coll.ensureIndex( {v1:1, v2:1, v3:1}) ● Result: Index-defined order 

Compound indexes (Part 2) ● Query: db.coll.find( {v1:{$in:[8, 4]},v2:8}) .sort({v3:1}) ● Index: db.coll.ensureIndex( {v1:1, v2:1, v3:1}) ● Result: In-memory sorting 

Compound indexes (Part 3) ● Query: db.coll.find( {v1:{$in:[8, 4]},v2:8}) .sort({v3:1}) ● Index: db.coll.ensureIndex( {v2:1, v3:1}) ● Result: Index-defined order 

Full-Text Search ● Document representation: { "v1" : "First string", "v2" : "Second string", "v3" : [{ "v4" : "String in nested list item" }], "_w" : ["first", "string", "second", "nested", "list", "item"] } ● Query representation: {"_w" : { $all : ["nested", "item"] }} 

Unique constraints (Part 1) ● Goals: – Unlimited count of unique constraints in one collection – Ability to define scope of uniqueness ● Solution: – Sparse and Unique index – List of unique tokens: _unique: [ "$", "$" ] 

Unique constraints (Part 2) Example: { "t" : "t1", "v1" : "tAg", "_u" : [ "t1$tag" ] } 

Resources ● Example scripts: https://github.com/kircul/mongodb- indexes-examples ● MongoDB: http://www.mongodb.org/ ● Indexes in MongoDB (Official documentation): http://www.mongodb.org/display/DOCS/Indexes ● Explain operation (Official documentation): http://www.mongodb.org/display/DOCS/Explain 

Indexing with MongoDB Kirill Duborenko EPAM Systems