Best Practices Using Indexes in MongoDB

1 .Best Practices Using Indexes in MongoDB Alexander Rubin 

2 . About Me My name is Alexander Rubin • Working with MySQL for over 12 years – Started at MySQL AB, then Sun Microsystems, – then Oracle (MySQL Consulting) – Joined Percona 5 years ago • Working with MongoDB for 3 years www.percona.com 

3 . Agenda • B-tree indexes • MongoDB Index types • Indexing Properties • Indexing Best Practices • Indexing practice: which index to create • Collect information about indexes • Monitoring and profiling slow queries www.percona.com 

4 .How to Deal with Slow Performance Indexes www.percona.com 

5 . MongoDB Index Types: B-Tree • MongoDB uses B-Tree indexes • Support equality and “range” operations http://en.wikipedia.org/wiki/B-tree www.percona.com 

6 . B-Tree: Const Equality search: db.movie.find({"title": "Star Trek"}) • Scan thru the tree and go directly to 1 leaf • Stop http://en.wikipedia.org/wiki/B-tree www.percona.com 

7 . B-Tree: Range Range: db.movie.find({"imdb.rating": {"$gte": 9} }) • Scan thru the tree and visit many leafs/nodes http://en.wikipedia.org/wiki/B-tree www.percona.com 

8 .How to create an index? db.collection.createIndex("field": 1) Number means sorting order 1 = Ascending -1 = Descending What is this number means? 

9 .How to list all indexes? > db.movies.getIndexes() [ { "v" : 2, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "movie.movies" } ] 

10 .Types of Indexes in MongoDB ● _id ● Simple ● Compound ● Multikey ● Full-Text ● Geo-spatial 

11 .Types of Indexes in MongoDB Default _id Index MongoDB creates a unique index on the _id field during the creation of a collection. Similar to this in MySQL: id int PRIMARY KEY auto_increment Default value is a 12-byte ObjectId: • 4-byte timestamp Monotonically • 3-byte machine id increasing • 2-byte process id • 3-byte counter 

12 .Single field index ● An index on a single key This is similar to a book’s index where you look up a word to find the pages it’s referenced on. ● Example > db.tweets.createIndex({"lang": 1}); { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } 

13 .Compound index ● Index is created over two or more* fields in a document This is similar to a phone book where you can find the phone number of a person given their first and last names. ● Example db.collection.createIndex( { <field1>: <type>, <field2>: <type2>, ... } ) * MongoDB imposes a limit of 31 fields for any compound index. 

14 .Multikey indexes, I To index a field that holds an array value ○ MongoDB creates an index key for each element in the array. ○ This means: if you have an array with 100 items, that document will have 100 index entries. 

15 .Multikey indexes, II Example: db.coll.createIndex( { <field>: < 1 or -1 > } ) Just create index normally... ● MongoDB automatically creates a multikey index if any indexed field is an array ● you do not need to explicitly specify the multikey type. 

16 .Text Indexes (Full Text) ● Text search queries on string content ● Similar to MySQL fulltext search ● Example: db.reviews.createIndex( { comments: "text" } ) 

17 .2dsphere Indexes (Geo Spatial) ● supports queries that calculate geometries on an earth-like sphere ● Example: > db.collection.createIndex( { <location field> : "2dsphere" } ) 

18 .Indexing Properties ● Unique ● Sparse ● TTL ● Partial 

19 .Indexing Properties: Unique ● Same as MySQL (other RDBMS) unique key > db.collection.createIndex({"email": 1}, {"unique": true}) ● can only have a single null or missing field value for all documents in the collection. 

20 .Indexing Properties: Sparse ● index only documents that contain a value for the given field. ● Especially important for MongoDB / Flexible schema ○ May be no value or property at all > db.collection.createIndex({"kids": 1}, {"sparse": true}) 

21 .Indexing Properties: TTL ● Great feature of MongoDB ● Allow to create collections which archived automatically > db.access_log.createIndex({"createdAt": 1}, {"expireAfterSeconds": 1200}) The indexed field should contain an ISODate() value. If any other type is used the document will not be removed. The TTL removal process runs once every 60 seconds so you might see the document even though the time has expired. 

22 .Indexing Properties: Partial ● Allows to index a subset of your data > db.collection.createIndex({"movie": 1, "reviews": 1}, {"rating": {"$gte": 4}}) The index will not be used if it would provide an incomplete result set (similar to the sparse index). 

23 .Indexing best practices Rule #1: Do NOT create an index, unless you know the queries which will benefit from it Too many indexes will harm MongoDB 

24 .Indexing best practices Rule #2: Use MongoDB profiler to help decide the indexes you need 

25 .Indexing practice: simple index > db.movie.find( {"title": "Star Trek"}, {_id:0, year:1, title:1, "imdb.rating":1} ) { "title" : "Star Trek", "year" : 2009, "imdb" : { "rating" : 8 } } 

26 .Indexing practice: simple index > db.movie.find({"title": "Star Trek"}).explain()["queryPlanner"] { "plannerVersion" : 1, "namespace" : "movie.movie", "indexFilterSet" : false, "parsedQuery" : { "title" : { "$eq" : "Star Trek" } I wish there }, will be an "winningPlan" : { "stage" : "COLLSCAN", index here... "filter" : { "title" : { "$eq" : "Star Trek" } }, "direction" : "forward" }, "rejectedPlans" : [ ] } 

27 .Indexing practice: simple index > db.movie.createIndex({"title":1}); { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } 

28 .Indexing practice: simple index > db.movie.find({"title": "Star Trek"}).explain()["queryPlanner"] { ... "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { Our dream "title" : 1 }, come true... "indexName" : "title_1", "isMultiKey" : false, "multiKeyPaths" : { "title" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "rejectedPlans" : [ ] 

29 .Indexing practice: compound index When creating a compound index: ● the sort order, ascending (1) or descending (-1), of the values starts to matter, especially if the index is used to sort on multiple keys. ● When creating compound indexes you want to add keys to the index in the following key order: ○ Equality matches ○ Sort fields ○ Inequality matches ● MongoDB can use leftmost part of the fields in index