indexing
DESCRIPTION
TRANSCRIPT
Mike Dirolf -‐ @mdirolf -‐ 10gen, Inc.
http://www.mongodb.org
Indexing
What’s Easy About MongoDB Indexing?
It’s almost the same as in your RDBMS
What’s Hard About MongoDB Indexing?
It’s almost the same as in your RDBMS
What is an Index?
Magic scaling sauce?
What is an Index?
A data structure that can be used to make certain queries more efficient.
Indexes Maintain OrderIndex on {a: 1}
{a: 0, b: 9}{a: 2, b: 0}
{a: 7, b: 1}
{a: 3, b: 2}
{a: 3, b: 5}{a: 3, b: 7}
{a: 9, b: 1}
Indexes Maintain OrderIndex on {a: 1, b: -‐1}
{a: 0, b: 9}{a: 2, b: 0}
{a: 7, b: 1}
{a: 3, b: 7}
{a: 3, b: 2}{a: 3, b: 5}
{a: 9, b: 1}
B-tree StructureIndex on {a: 1}
{...} {...} {...} {...} {...} {...} {...} {...} {...} {...} {...}
[-∞, 5) [5, 10) [10, ∞)
[5, 7) [7, 9) [9, 10) [10, ∞) buckets[-∞, 5) buckets
Query for {a: 7}
{...} {...} {...} {...} {...} {...} {...} {...} {...} {...} {...}
[-∞, 5) [5, 10) [10, ∞)
[5, 7) [7, 9) [9, 10) [10, ∞) buckets[-∞, 5) buckets
Index
Scan
Creating Indexes
db.posts.ensureIndex({“name”: 1})
An index on _id is automatic.
For more, ensureIndex:
Compound Indexes
db.posts.ensureIndex({name: 1, date: -‐1})
Unique Indexes
db.posts.ensureIndex({title: 1}, {unique: true})
Background Builds
db.posts.ensureIndex(..., {background: true})
Indexing Embedded Documents
db.posts.ensureIndex({“comments.author”: 1})
Multikeys
{“tags”: [“mongodb”, “indexing”], ...}
db.posts.ensureIndex({“tags”: 1})
Geospatial
db.posts.ensureIndex({“location”: “2d”})
Listing Indexes
db.posts.getIndexes()
Dropping an Index
db.posts.dropIndex({“tags”: 1})
When is an Index Used?Index on {a: 1}
db.collection.find({a: 0})db.collection.find({a: {$in: [0, 2]}})db.collection.find({a: {$gt: 5}})db.collection.count({a: 0})db.collection.find().sort({a: -‐1})
db.collection.find({b: 0}).sort({a: -‐1})Partially:
When isn’t an Index Used?Index on {a: 1, b: -‐1}
db.collection.find({b: 0})
As a rule: try imagining how the sorted representation could help the
server with your query.
Picking an Indexfind({x: 10, y: “foo”})
scan
index on x
index on y remember
terminate
When are Indexes Needed?
Frequently used queries
Low response time
Indexes Take Up Space
db.collection.totalIndexSize()
Indexes Slow Down Writes
Explaindb.collection.find(query).explain();
{ "cursor" : "BasicCursor", "indexBounds" : [ ], "nscanned" : 57594, "nscannedObjects" : 57594, "n" : 3 , "millis" : 108}
Explain
{ "cursor" : "BtreeCursor x_1", "indexBounds" : [ ], "nscanned" : 123, "nscannedObjects" : 123, "n" : 10 , "millis" : 4}
