🧪 Lab 03: Indexes¶

Topics: Single-field, compound, unique, multikey, text, partial, sparse, TTL

---

In [1]:

from pymongo import MongoClient, ReadPreference
from pymongo.read_concern import ReadConcern
from pymongo.write_concern import WriteConcern
from pymongo.errors import ConnectionFailure
from bson import ObjectId
from datetime import datetime, timedelta, timezone
import pprint

USE_ATLAS = False
ATLAS_URI  = "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/?retryWrites=true&w=majority"
DOCKER_URI = "mongodb://127.0.0.1:27017/?directConnection=true"

uri = ATLAS_URI if USE_ATLAS else DOCKER_URI
client = MongoClient(uri, serverSelectionTimeoutMS=5000)
try:
    client.admin.command("ping")
    print("✅ Connected to", "Atlas" if USE_ATLAS else "Docker MongoDB")
except ConnectionFailure as e:
    print("❌ Connection failed:", e)

db = client["mongo_labs"]
pp = pprint.PrettyPrinter(indent=2)

from pymongo import MongoClient, ReadPreference from pymongo.read_concern import ReadConcern from pymongo.write_concern import WriteConcern from pymongo.errors import ConnectionFailure from bson import ObjectId from datetime import datetime, timedelta, timezone import pprint USE_ATLAS = False ATLAS_URI = "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/?retryWrites=true&w=majority" DOCKER_URI = "mongodb://127.0.0.1:27017/?directConnection=true" uri = ATLAS_URI if USE_ATLAS else DOCKER_URI client = MongoClient(uri, serverSelectionTimeoutMS=5000) try: client.admin.command("ping") print("✅ Connected to", "Atlas" if USE_ATLAS else "Docker MongoDB") except ConnectionFailure as e: print("❌ Connection failed:", e) db = client["mongo_labs"] pp = pprint.PrettyPrinter(indent=2)

✅ Connected to Docker MongoDB

In [2]:

print("Indexes on products:")
for idx in db.products.index_information().values():
    print(f"  {idx['key']}")

print("Indexes on products:") for idx in db.products.index_information().values(): print(f" {idx['key']}")

Indexes on products:
  [('_id', 1)]

Single-field Index¶

In [3]:

db.products.create_index([("price",1)],name="idx_price_asc")
print("Created single-field index on price")

db.products.create_index([("price",1)],name="idx_price_asc") print("Created single-field index on price")

Created single-field index on price

Compound Index (ESR rule)¶

In [4]:

db.products.create_index([("category",1),("price",-1)],name="idx_category_price")
print("Created compound index")

db.products.create_index([("category",1),("price",-1)],name="idx_category_price") print("Created compound index")

Created compound index

Unique Index¶

In [5]:

db.users.drop()
db.users.insert_many([{"name":"Alice","email":"alice@example.com"},{"name":"Bob","email":"bob@example.com"}])
db.users.create_index([("email",1)],unique=True,name="idx_users_email")
print("Created unique index")

db.users.drop() db.users.insert_many([{"name":"Alice","email":"alice@example.com"},{"name":"Bob","email":"bob@example.com"}]) db.users.create_index([("email",1)],unique=True,name="idx_users_email") print("Created unique index")

Created unique index

Multikey Index (on arrays)¶

In [6]:

db.users.create_index([("tags",1)],name="idx_users_tags")
db.users.insert_one({"name":"Carol","tags":["premium","early-adopter"]})
for u in db.users.find({"tags":"premium"},{"_id":0,"name":1}):
    print(u["name"])

db.users.create_index([("tags",1)],name="idx_users_tags") db.users.insert_one({"name":"Carol","tags":["premium","early-adopter"]}) for u in db.users.find({"tags":"premium"},{"_id":0,"name":1}): print(u["name"])

Carol

Text Index¶

In [7]:

db.products.create_index([("name","text"),("category","text")],name="idx_products_text")
results = list(db.products.find({"$text":{"$search":"keyboard"}},{"score":{"$meta":"textScore"},"name":1,"_id":0}))
print("Text search:", [r["name"] for r in results])

db.products.create_index([("name","text"),("category","text")],name="idx_products_text") results = list(db.products.find({"$text":{"$search":"keyboard"}},{"score":{"$meta":"textScore"},"name":1,"_id":0})) print("Text search:", [r["name"] for r in results])

Text search: []

Partial Index¶

In [8]:

db.orders.drop()
db.orders.insert_many([{"userId":"a","status":"pending"},{"userId":"b","status":"delivered"}])
db.orders.create_index([("userId",1)],partialFilterExpression={"status":{"$in":["pending","shipped"]}},name="idx_orders_active")
print("Created partial index")

db.orders.drop() db.orders.insert_many([{"userId":"a","status":"pending"},{"userId":"b","status":"delivered"}]) db.orders.create_index([("userId",1)],partialFilterExpression={"status":{"$in":["pending","shipped"]}},name="idx_orders_active") print("Created partial index")

Created partial index

Sparse Index¶

In [9]:

db.products.create_index([("onSale",1)],sparse=True,name="idx_products_onsale")
print("Created sparse index")

db.products.create_index([("onSale",1)],sparse=True,name="idx_products_onsale") print("Created sparse index")

Created sparse index

TTL Index¶

In [10]:

db.sessions.drop()
db.sessions.create_index([("createdAt",1)],expireAfterSeconds=3600,name="idx_sessions_ttl")
print("Created TTL index")

db.sessions.drop() db.sessions.create_index([("createdAt",1)],expireAfterSeconds=3600,name="idx_sessions_ttl") print("Created TTL index")

Created TTL index

explain() - Check index usage¶

In [11]:

plan = db.products.find({"category":"peripherals","price":{"$lt":100}}).explain("executionStats")
print("Stage:", plan["queryPlanner"]["winningPlan"]["stage"])
print("Docs examined:", plan["executionStats"]["totalDocsExamined"])

plan = db.products.find({"category":"peripherals","price":{"$lt":100}}).explain("executionStats") print("Stage:", plan["queryPlanner"]["winningPlan"]["stage"]) print("Docs examined:", plan["executionStats"]["totalDocsExamined"])

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[11], line 1
----> 1 plan = db.products.find({"category":"peripherals","price":{"$lt":100}}).explain("executionStats")
      2 print("Stage:", plan["queryPlanner"]["winningPlan"]["stage"])
      3 print("Docs examined:", plan["executionStats"]["totalDocsExamined"])

TypeError: explain() takes 1 positional argument but 2 were given

Covered Query¶

In [ ]:

covered = db.products.find({"category":"peripherals"},{"_id":0,"category":1,"price":1}).explain("executionStats")
print("Covered query stage:", covered["queryPlanner"]["winningPlan"]["stage"])

covered = db.products.find({"category":"peripherals"},{"_id":0,"category":1,"price":1}).explain("executionStats") print("Covered query stage:", covered["queryPlanner"]["winningPlan"]["stage"])