{"id":514,"date":"2018-02-14T20:30:24","date_gmt":"2018-02-14T12:30:24","guid":{"rendered":"https:\/\/vinta.ws\/code\/?p=514"},"modified":"2026-03-17T01:16:31","modified_gmt":"2026-03-16T17:16:31","slug":"mongodb-cookbook-indexes","status":"publish","type":"post","link":"https:\/\/vinta.ws\/code\/mongodb-cookbook-indexes.html","title":{"rendered":"MongoDB cookbook: Indexes"},"content":{"rendered":"

Indexes are crucial for the efficient execution of queries and aggregations in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e., scan every document in a collection.<\/p>\n

If a write operation modifies an indexed field, MongoDB updates all indexes that have the modified field as a key. So, be careful while choosing indexes.<\/p>\n

Types Of Indexes<\/h2>\n

ref:
\nhttps:\/\/docs.mongodb.com\/manual\/indexes\/<\/a>
\nhttps:\/\/docs.mongodb.com\/manual\/applications\/indexes\/<\/a><\/p>\n

Single Field Index<\/h3>\n

For a single field index and sort operations, the sort order (i.e. ascending or descending) of the index key doesn't matter. With index intersection, single field indexes could be powerful.<\/p>\n

ref:
\nhttps:\/\/docs.mongodb.com\/manual\/core\/index-single\/<\/a><\/p>\n

Compound Index<\/h3>\n

The order of the fields listed in a compound index is very important.<\/p>\n

ref:
\nhttps:\/\/docs.mongodb.com\/manual\/core\/index-compound\/<\/a>
\nhttps:\/\/docs.mongodb.com\/manual\/tutorial\/create-indexes-to-support-queries\/<\/a><\/p>\n

TTL Index<\/h3>\n

When the TTL thread is active, a background thread in mongod reads the values in the index and removes expired documents from the collection. You will see delete operations in the output of db.currentOp()<\/code>.<\/p>\n

TTL indexes are single-field indexes. Compound indexes do not support TTL and ignore the expireAfterSeconds<\/code> option.<\/p>\n

import datetime\n\nclass JournalEntry(db.Document):\n    users = db.ListField(db.ReferenceField('User'))\n    event = db.StringField()\n    context = db.DynamicField()\n    timestamp = db.DateTimeField(default=datetime.datetime.utcnow)\n\n    meta = {\n        'index_background': True,\n        'indexes': [\n            {\n                'fields': ['timestamp'],\n                'cls': False,\n                'expireAfterSeconds': int(datetime.timedelta(days=90).total_seconds()),\n            },\n        ],\n    }<\/code><\/pre>\n
ref:
\nhttps:\/\/docs.mongodb.com\/manual\/core\/index-ttl\/<\/a><\/p>\n
Index Intersection<\/h3>\n
MongoDB can use multiple single field indexes to fulfill queries.<\/p>\n
db.orders.createIndex({tags: 1});\ndb.orders.createIndex({key: { created_at: -1 }, background: true});\n\ndb.orders.find({item: 'abc123', qty: {$gt: 15}});<\/code><\/pre>\n
ref:
\nhttps:\/\/docs.mongodb.com\/manual\/core\/index-intersection\/<\/a><\/p>\n
Covered Queries<\/h3>\n
ref:
\nhttps:\/\/docs.mongodb.com\/manual\/core\/query-optimization\/#read-operations-covered-query<\/a><\/p>\n
Index Limits<\/h2>\n
The size of an  index entry for an indexed field must be less than 1024 bytes. For instance, an arbitrary URL field can easily exceed 1024 bytes. <\/p>\n
MongoDB will not insert into an indexed collection any document with an indexed field whose corresponding index entry would exceed the index key limit, and instead will return an error. Updates to the indexed field will error if the updated value causes the index entry to exceed the index key limit.<\/p>\n
ref:
\nhttps:\/\/docs.mongodb.com\/manual\/reference\/limits\/#indexes<\/a><\/p>\n
List Indexes<\/h2>\n
db.message.getIndexes()\n\n\/\/ show collection statistics\ndb.message.stats()\ndb.message.stats().indexSizes\n\n\/\/ scale defaults to 1 to return size data in bytes\n\/\/ 1024 * 1024 means MB\ndb.getCollection('message').stats({'scale': 1024 * 1024}).indexSizes<\/code><\/pre>\n
ref:
\nhttps:\/\/docs.mongodb.com\/manual\/tutorial\/manage-indexes\/<\/a><\/p>\n
Add Indexes<\/h2>\n
TODO:
\nIt seems like creating indexes on empty collection, even with background will cause DB latency.<\/p>\n
An index which contains array fields might consume a lot of disk space.<\/p>\n
db.message.createIndex({\n    '_cls': 1,\n    'sender': 1,\n    'posted_at': 1\n}, {'background': true, 'sparse': true})\n\ndb.message.createIndex({\n    '_cls': 1,\n    'includes': 1,\n    'posted_at': 1\n}, {'background': true, 'sparse': true})\n\ndb.getCollection('message').find({\n    '$or': [\n        \/\/ sent by cp\n        {\n            '_cls': 'Message.ChatMessage',\n            'sender': ObjectId('582ee32a5b9c861c87dc297e'),\n            'posted_at': {\n                '$gte': ISODate('2018-01-08T00:00:00.000Z'),\n                '$lt': ISODate('2018-01-14T00:00:00.000Z')\n            }\n        },\n        \/\/ sent by payer\n        {\n            '_cls': 'Message.GiftMessage',\n            'includes': ObjectId('582ee32a5b9c861c87dc297e'),\n            'posted_at': {\n                '$gte': ISODate('2018-01-08T00:00:00.000Z'),\n                '$lt': ISODate('2018-01-14T00:00:00.000Z')\n            }\n        }\n    ]\n})<\/code><\/pre>\n
import pymongo\nfrom your_app.models import YourModel\n\nYourModel._get_collection().create_index(\n    [\n        ('users', pymongo.ASCENDING),\n        ('timestamp', pymongo.DESCENDING),\n    ], \n    background=True,\n    partialFilterExpression={'timestamp': {'$exists': True}},\n)<\/code><\/pre>\n
ref:
\nhttp:\/\/api.mongodb.com\/python\/current\/api\/pymongo\/collection.html#pymongo.collection.Collection.create_index<\/a><\/p>\n
You can't index two arrays together, in this example: includes<\/code> and unlocks<\/code>.<\/p>\n
\/\/ it doesn't work\ndb.message.createIndex({\n    '_cls': 1,\n    'sender': 1,\n    'includes': 1,\n    'unlocks': 1\n}, {'background': true, 'sparse': true})<\/code><\/pre>\n
The Order Of Fields of Compound Indexes<\/h3>\n
The order of fields in an index matters, you must consider Index Cardinality<\/strong> and Selectivity<\/strong>. Instead, the order of fields in a find()<\/code> query or $match<\/code> in an aggregation doesn't affect whether it can use an index or not.<\/p>\n
The order of fields in a compound index should be:<\/p>\n