Database Indexing: Optimize Query Performance

Database Indexing: Optimize Query Performance

Database indexes speed up queries by orders of magnitude. Without index on user.email, querying 1 million users scans all rows (slow). With index, database uses binary search (fast). Indexes trade storage space for query speed. Index on email column requires extra storage but makes lookups instant. Query SELECT * FROM users WHERE email = 'user@example.com' takes milliseconds with index, seconds without.

How Indexes Work

Index creates sorted data structure (B-tree, hash table) mapping column values to row locations. B-tree index on email maintains sorted order: 'a@...' → rows 5, 12, 18; 'b@...' → rows 3, 8, 22. Binary search through index finds target value in log(n) steps. Lookup "john@example.com": compare middle value, pick smaller/larger half recursively until found in ~20 steps (vs 500K scans). Hash index trades ordering for O(1) lookup but only for exact matches (WHERE email = 'x'). B-tree works for ranges (WHERE email LIKE 'a%').

Types of Indexes

Primary Key Index: Automatically created, ensures uniqueness. Identifies each row uniquely. Single Column Index: Index on one column. Create: CREATE INDEX idx_email ON users(email). Used for: WHERE email = 'x', WHERE age > 30. Composite Index: Index on multiple columns. CREATE INDEX idx_name_age ON users(first_name, last_name). Used for: WHERE first_name = 'x' AND last_name = 'y'. Column order matters: (first_name, last_name) index used for WHERE first_name = 'x' but NOT for WHERE last_name = 'y'. Full-text Index: For text search. Faster phrase matching than LIKE. Unique Index: Enforces uniqueness like primary key. CREATE UNIQUE INDEX idx_email ON users(email). Foreign Key Index: Improves JOIN performance.

Query Optimization with Indexes

Query: SELECT id, name FROM users WHERE email = 'john@example.com'. Without index: Full table scan, 1M rows checked (slow). With index on email: Binary search index, finds row instantly. EXPLAIN query to see execution plan: EXPLAIN SELECT * FROM users WHERE email = 'x'. Output shows: Seq Scan (no index) vs Index Scan (index used). Look for "Index Cond" indicating index used. If "Seq Scan" for indexed column, statistics may be outdated: ANALYZE users; updates statistics so optimizer chooses index.

Index Selectivity

Selectivity: Percentage of rows matching condition. High selectivity (< 1% rows match): Index valuable. Index query returns few rows, no need scan rest. Low selectivity (> 10% rows match): Index less helpful. Index narrows down to 100K rows; full scan 1M rows 10x difference, but still need read many. Too many duplicates waste index. Example: gender column (2 values out of 1M rows). Index on gender wastes space; not selective. Selectivity = (distinct values / total rows). Aim for selectivity > 1%. Index on email (unique): selectivity = 100%; always useful. Index on country (50 distinct): selectivity = 0.005%; might not be useful.

Index Maintenance

Indexes must be updated on every INSERT, UPDATE, DELETE. Insert row → Index also updated. More indexes → More maintenance overhead. Choose indexes carefully; unnecessary indexes slow writes. Monitor index usage: PostgreSQL: pg_stat_user_indexes shows index scans, unused indexes. MySQL: PERFORMANCE_SCHEMA tracks index usage. Drop unused indexes: DROP INDEX idx_unused ON table_name. Rebuilding: Over time, indexes become fragmented, queries slower. Rebuild index: REINDEX INDEX idx_name; Rebuilds index from scratch, defragments. Set automatic rebuilds weekly.

Composite Index Design

Query: SELECT * FROM orders WHERE status = 'pending' AND created_date > '2026-01-01' AND customer_id = 5. Single indexes on each column less optimal. Composite index: CREATE INDEX idx_status_date_customer ON orders(status, created_date, customer_id). Order matters: Put equality conditions first (status), then range conditions (created_date), then remaining. Index used if query has status and created_date conditions. If query only has created_date, index not used (status must be first in WHERE). Design indexes based on actual query patterns from slow query logs.

Covering Indexes

Query: SELECT id, email FROM users WHERE status = 'active'. Regular index on status: finds matching rows by status, then fetches row data for id and email columns. Covering index: CREATE INDEX idx_status_covering ON users(status) INCLUDE (id, email). Index contains all needed columns. Query satisfied entirely by index without row fetch (Index Only Scan). Much faster; no additional disk access. Covers ~5% of queries in typical app; high ROI for slow queries.

Partial Indexes

Index all rows wasteful if filtering common. Most orders inactive/closed. Active orders often queried. Partial index: CREATE INDEX idx_active_orders ON orders(customer_id) WHERE status = 'active'. Indexes only active order rows, much smaller, faster. Only used for queries with WHERE status = 'active' condition. Reduces index size and maintenance overhead. Useful for time-series: archive old events, only index recent.

Avoiding Index Pitfalls

Over-indexing: Creating index on every column is wasteful. Each index consumes storage and slows writes. Analyze slow queries; create indexes only for actual bottlenecks. Unused indexes: Regular cleanup drops indexes unused in past month. Functions on indexed columns: WHERE LOWER(email) = 'x' doesn't use email index; must be WHERE email = LOWER('x') or create functional index. Prefix indexes: Indexing first 10 characters of long text saves space. Watch for unintended full scans in application code.

Query Analysis

Enable slow query log: Set long_query_time = 0.1 seconds; log queries slower than 100ms. Analyze slowest queries daily. Add indexes targeting high-impact queries. Sample query: Takes 5 seconds, 10K executions daily = 50K seconds wasted daily. Single index reduces to 0.01 seconds = 1% of previous time. RUN EXPLAIN ANALYZE to see actual vs estimated row counts. If estimates off, update table statistics. Indexes improve over time; monitor and adjust.

Under the Hood: Database Indexing: Optimize Query Performance

Here is what separates someone who merely USES technology from someone who UNDERSTANDS it: knowing what happens behind the screen. When you tap "Send" on a WhatsApp message, do you know what journey that message takes? When you search something on Google, do you know how it finds the answer among billions of web pages in less than a second? When UPI processes a payment, what makes sure the money goes to the right person?

Understanding Database Indexing: Optimize Query Performance gives you the ability to answer these questions. More importantly, it gives you the foundation to BUILD things, not just use things other people built. India's tech industry employs over 5 million people, and companies like Infosys, TCS, Wipro, and thousands of startups are all built on the concepts we are about to explore.

This is not just theory for exams. This is how the real world works. Let us get into it.

Object-Oriented Programming: Modelling the Real World

OOP lets you model real-world entities as code "objects." Each object has properties (data) and methods (behaviour). Here is a practical example:

class BankAccount:
    """A simple bank account — like what SBI or HDFC uses internally"""

    def __init__(self, holder_name, initial_balance=0):
        self.holder = holder_name
        self.balance = initial_balance    # Private in practice
        self.transactions = []            # History log

    def deposit(self, amount):
        if amount <= 0:
            raise ValueError("Deposit must be positive")
        self.balance += amount
        self.transactions.append(f"+₹{amount}")
        return self.balance

    def withdraw(self, amount):
        if amount > self.balance:
            raise ValueError("Insufficient funds!")
        self.balance -= amount
        self.transactions.append(f"-₹{amount}")
        return self.balance

    def statement(self):
        print(f"
--- Account Statement: {self.holder} ---")
        for t in self.transactions:
            print(f"  {t}")
        print(f"  Balance: ₹{self.balance}")

# Usage
acc = BankAccount("Rahul Sharma", 5000)
acc.deposit(15000)      # Salary credited
acc.withdraw(2000)      # UPI payment to Swiggy
acc.withdraw(500)       # Metro card recharge
acc.statement()

This is encapsulation — bundling data and behaviour together. The user of BankAccount does not need to know HOW deposit works internally; they just call it. Inheritance lets you extend this: a SavingsAccount could inherit from BankAccount and add interest calculation. Polymorphism means different account types can respond to the same .withdraw() method differently (savings accounts might check minimum balance, current accounts might allow overdraft).

Real-World System Design: Swiggy's Architecture

When you order food on Swiggy, here is what happens behind the scenes in about 2 seconds: your location is geocoded (algorithms), nearby restaurants are queried from a spatial index (data structures), menu prices are pulled from a database (SQL), delivery time is estimated using ML models trained on historical data (AI), the order is placed in a distributed message queue (Kafka), a delivery partner is assigned using a matching algorithm (optimization), and real-time tracking begins using WebSocket connections (networking). EVERY concept in your CS curriculum is being used simultaneously to deliver your biryani.

How the Web Request Cycle Works

Every time you visit a website, a precise sequence of events occurs. Here is the flow:

    You (Browser)          DNS Server          Web Server
        |                      |                    |
        |---[1] bharath.ai --->|                    |
        |                      |                    |
        |<--[2] IP: 76.76.21.9|                    |
        |                      |                    |
        |---[3] GET /index.html ----------------->  |
        |                      |                    |
        |                      |    [4] Server finds file,
        |                      |        runs server code,
        |                      |        prepares response
        |                      |                    |
        |<---[5] HTTP 200 OK + HTML + CSS + JS --- |
        |                      |                    |
   [6] Browser parses HTML                          |
       Loads CSS (styling)                          |
       Executes JS (interactivity)                  |
       Renders final page                           |

Step 1-2 is DNS resolution — converting a human-readable domain name to a machine-readable IP address. Step 3 is the HTTP request. Step 4 is server-side processing (this is where frameworks like Node.js, Django, or Flask operate). Step 5 is the HTTP response. Step 6 is client-side rendering (this is where React, Angular, or Vue operate).

In a real-world scenario, this cycle also involves CDNs (Content Delivery Networks), load balancers, caching layers, and potentially microservices. Indian companies like Jio use this exact architecture to serve 400+ million subscribers.

Production Engineering: Database Indexing: Optimize Query Performance at Scale

Understanding database indexing: optimize query performance at an academic level is necessary but not sufficient. Let us examine how these concepts manifest in production environments where failure has real consequences.

Consider India's UPI system processing 10+ billion transactions monthly. The architecture must guarantee: atomicity (a transfer either completes fully or not at all — no half-transfers), consistency (balances always add up correctly across all banks), isolation (concurrent transactions on the same account do not interfere), and durability (once confirmed, a transaction survives any failure). These are the ACID properties, and violating any one of them in a payment system would cause financial chaos for millions of people.

At scale, you also face the thundering herd problem: what happens when a million users check their exam results at the same time? (CBSE result day, anyone?) Without rate limiting, connection pooling, caching, and graceful degradation, the system crashes. Good engineering means designing for the worst case while optimising for the common case. Companies like NPCI (the organisation behind UPI) invest heavily in load testing — simulating peak traffic to identify bottlenecks before they affect real users.

Monitoring and observability become critical at scale. You need metrics (how many requests per second? what is the 99th percentile latency?), logs (what happened when something went wrong?), and traces (how did a single request flow through 15 different microservices?). Tools like Prometheus, Grafana, ELK Stack, and Jaeger are standard in Indian tech companies. When Hotstar streams IPL to 50 million concurrent users, their engineering team watches these dashboards in real-time, ready to intervene if any metric goes anomalous.

The career implications are clear: engineers who understand both the theory (from chapters like this one) AND the practice (from building real systems) command the highest salaries and most interesting roles. India's top engineering talent earns ₹50-100+ LPA at companies like Google, Microsoft, and Goldman Sachs, or builds their own startups. The foundation starts here.

Key Vocabulary

Here are important terms from this chapter that you should know:

💡 Interview-Style Problem

Here is a problem that frequently appears in technical interviews at companies like Google, Amazon, and Flipkart: "Design a URL shortener like bit.ly. How would you generate unique short codes? How would you handle millions of redirects per second? What database would you use and why? How would you track click analytics?"

Think about: hash functions for generating short codes, read-heavy workload (99% redirects, 1% creates) suggesting caching, database choice (Redis for cache, PostgreSQL for persistence), and horizontal scaling with consistent hashing. Try sketching the system architecture on paper before looking up solutions. The ability to think through system design problems is the single most valuable skill for senior engineering roles.

Where This Takes You

The knowledge you have gained about database indexing: optimize query performance is directly applicable to: competitive programming (Codeforces, CodeChef — India has the 2nd largest competitive programming community globally), open-source contribution (India is the 2nd largest contributor on GitHub), placement preparation (these concepts form 60% of technical interview questions), and building real products (every startup needs engineers who understand these fundamentals).

India's tech ecosystem offers incredible opportunities. Freshers at top companies earn ₹15-50 LPA; experienced engineers at FAANG companies in India earn ₹50-1 Cr+. But more importantly, the problems being solved in India — digital payments for 1.4 billion people, healthcare AI for rural areas, agricultural tech for 150 million farmers — are some of the most impactful engineering challenges in the world. The fundamentals you are building will be the tools you use to tackle them.

Crafted for Class 7–9 • Programming & Coding • Aligned with NEP 2020 & CBSE Curriculum