Query Performance

🔍 NoSQL Query Performance: Making Your Database Lightning Fast

The Detective Story of Slow Queries

Imagine you’re a detective 🕵️ in a giant library. Someone asks you to find a specific book. If you search randomly, it takes forever. But if you have a smart system—knowing exactly which shelf, which section—you find it in seconds!

That’s what query performance is all about. Your NoSQL database is the library. Your queries are the requests. And YOU are about to become the master detective who makes everything fast!

🎯 What We’ll Master Together

graph TD
    A["Query Performance"] --> B["🔧 Query Optimization"]
    A --> C["📋 Explain Plans"]
    A --> D["🐢 Slow Query Logs"]
    A --> E["📊 Performance Analysis"]

    style A fill:#FF6B6B,color:#fff
    style B fill:#4ECDC4,color:#fff
    style C fill:#45B7D1,color:#fff
    style D fill:#96CEB4,color:#fff
    style E fill:#FFEAA7,color:#333

🔧 Query Optimization: The Art of Finding Things Fast

What Is It?

Think of query optimization like choosing the best route to school. You could:

• Walk through every street (SLOW! 🐢)
• Take the main road (BETTER! 🚶)
• Use a shortcut you know (FASTEST! 🏃)

Query optimization = finding the shortcut for your database queries.

The Golden Rules

1. Use Indexes (Like a Book’s Table of Contents)

// WITHOUT index - searches EVERYTHING 🐢
db.users.find({ email: "anna@example.com" })
// Database looks at ALL million users!

// WITH index - jumps straight there 🚀
db.users.createIndex({ email: 1 })
db.users.find({ email: "anna@example.com" })
// Database finds Anna instantly!

Real-Life Example:

• Without index: Looking for “elephant” by reading every page of a dictionary
• With index: Using alphabetical tabs to jump to “E” section

2. Only Ask for What You Need (Projection)

// BAD: Getting everything when you only need name
db.users.find({ age: 25 })
// Returns: name, email, address, phone,
//          history, preferences... SO MUCH DATA!

// GOOD: Get only what you need
db.users.find(
  { age: 25 },
  { name: 1, email: 1 }
)
// Returns: just name and email. Fast and light!

Think of it like: Ordering just fries 🍟 instead of the whole menu when you’re only hungry for fries!

3. Limit Your Results

// BAD: Give me ALL matching users
db.users.find({ country: "USA" })
// Could return millions of records!

// GOOD: Just give me the first 10
db.users.find({ country: "USA" }).limit(10)
// Quick and manageable!

4. Smart Query Patterns

📋 Explain Plans: Your Query’s Report Card

What Is It?

Remember getting report cards in school? They tell you how well you did, right?

Explain Plans are report cards for your queries. They tell you:

• How the database found your data
• How long it took
• How many documents it looked at
• Whether it used an index or not

How to Get an Explain Plan

// Add .explain() to any query
db.users.find({ email: "test@example.com" })
        .explain("executionStats")

Reading the Report Card

// Key things to look at:
{
  "winningPlan": {
    "stage": "IXSCAN",     // GOOD! Used an index
    // vs "COLLSCAN"       // BAD! Scanned everything
  },
  "executionStats": {
    "totalDocsExamined": 1,      // How many looked at
    "totalKeysExamined": 1,      // How many index entries
    "executionTimeMillis": 0     // How fast (lower=better)
  }
}

The Magic Words to Know

Real Example

// Query
db.orders.find({
  status: "pending",
  date: { $gt: new Date("2024-01-01") }
}).explain("executionStats")

// Good Result ✅
{
  "stage": "IXSCAN",
  "indexName": "status_date_idx",
  "totalDocsExamined": 50,
  "executionTimeMillis": 2
}

// Bad Result ❌
{
  "stage": "COLLSCAN",
  "totalDocsExamined": 1000000,
  "executionTimeMillis": 5000
}

🐢 Slow Query Logs: Catching the Troublemakers

What Is It?

Imagine your teacher keeps a list of students who always arrive late to class. That’s exactly what Slow Query Logs do!

They catch and record queries that take too long, so you can fix them.

Setting Up Slow Query Logs

// MongoDB: Enable profiling
// Level 0 = off
// Level 1 = slow queries only
// Level 2 = all queries

db.setProfilingLevel(1, { slowms: 100 })
// Now it logs any query taking > 100ms

Finding the Troublemakers

// See all slow queries
db.system.profile.find().sort({ ts: -1 })

// Find the SLOWEST query
db.system.profile.find().sort({ millis: -1 }).limit(1)

// Find slow queries on a specific collection
db.system.profile.find({
  ns: "mydb.users",
  millis: { $gt: 1000 }  // Over 1 second
})

What the Log Tells You

{
  "op": "query",           // What type of operation
  "ns": "mydb.users",      // Which collection
  "query": {...},          // The actual query
  "millis": 5234,          // How long it took
  "ts": ISODate(...),      // When it happened
  "planSummary": "COLLSCAN" // Did it use index?
}

Common Troublemakers and Fixes

📊 Query Performance Analysis: The Big Picture

What Is It?

Performance Analysis is like being a doctor for your database. You check:

• Is it healthy?
• Where does it hurt?
• What medicine does it need?

The Health Check Dashboard

graph TD
    A["📊 Performance Analysis"] --> B["Response Time"]
    A --> C["Throughput"]
    A --> D["Resource Usage"]
    A --> E["Error Rate"]

    B --> B1["How fast?"]
    C --> C1["How many per second?"]
    D --> D1["CPU, Memory, Disk?"]
    E --> E1["How many failures?"]

    style A fill:#FF6B6B,color:#fff

Key Metrics to Watch

1. Response Time (How Fast?)

// Check average response time
db.serverStatus().opcounters
db.serverStatus().metrics.queryExecutor

2. Throughput (How Many?)

// Operations per second
db.serverStatus().opcounters
// Shows: insert, query, update, delete counts

3. Index Usage Stats

// See which indexes are actually being used
db.users.aggregate([
  { $indexStats: {} }
])

// Result shows:
{
  "name": "email_1",
  "accesses": {
    "ops": 15234,    // Times this index was used
    "since": ISODate(...)
  }
}

4. The Analysis Process

graph TD
    A["🔍 Identify Slow Query"] --> B["📋 Get Explain Plan"]
    B --> C{Using Index?}
    C -->|No| D["Create Index"]
    C -->|Yes| E{Right Index?}
    E -->|No| F["Create Better Index"]
    E -->|Yes| G["Check Query Pattern"]
    D --> H["✅ Test Again"]
    F --> H
    G --> H

    style A fill:#FF6B6B,color:#fff
    style H fill:#4ECDC4,color:#fff

Real-World Analysis Example

The Patient: A query taking 5 seconds

Step 1: Get the explain plan

db.orders.find({
  customerId: "abc123",
  status: "shipped",
  orderDate: { $gt: new Date("2024-01-01") }
}).explain("executionStats")

Step 2: Diagnosis

// Found: COLLSCAN, examined 2 million documents
// Problem: No useful index!

Step 3: Treatment

// Create a compound index
db.orders.createIndex({
  customerId: 1,
  status: 1,
  orderDate: -1
})

Step 4: Check Recovery

// Run explain again
// Now: IXSCAN, examined 47 documents
// Time: 3ms instead of 5000ms!

🎓 Summary: Your Query Performance Toolkit

🚀 Quick Wins to Remember

1. Always use indexes on fields you search or sort by
2. Only select fields you need (projection)
3. Use .limit() when you don’t need everything
4. Check explain plans for queries taking too long
5. Enable slow query logs in production
6. Analyze regularly - databases change over time!

💡 The Detective’s Final Wisdom

Remember our library detective? Here’s what they learned:

“A fast database isn’t magic. It’s just smart organization. Know where things are (indexes), don’t grab more than you need (projection), and always review your methods (explain plans). That’s the secret!”

You now have all the tools to make your NoSQL queries lightning fast! ⚡

Go forth and optimize! 🎉