Indexing 🚀 — Making Database Queries Faster

A database index is a data structure that helps the database find rows faster without scanning the entire table.

flowchart LR
    A["Application"] --> Q["SQL Query"]
    Q --> I["Index"]
    I --> T["Required Rows"]

    Q -. "Without Index" .-> S["Full Table Scan"]

1. Without an Index

Suppose a table contains millions of customers and we search by email. Without a suitable index, the database may need to examine many rows.

flowchart LR
    Q["WHERE Email = 'user@example.com'"] --> T["Customer Table"]
    T --> R1["Row 1"]
    T --> R2["Row 2"]
    T --> RN["... Millions of Rows"]

2. With an Index

An index allows the database to locate matching rows more efficiently.

flowchart LR
    Q["WHERE Email = 'user@example.com'"] --> I["Email Index"]
    I --> R["Matching Row"]
    R --> T["Customer Table"]

3. Example

SELECT *
FROM Customers
WHERE Email = 'user@example.com';

An index on Email can make this lookup much faster, especially when the table is large and the predicate is selective.

CREATE INDEX IX_Customers_Email
ON Customers(Email);

4. Common Types of Indexes

• Clustered Index — determines the physical/logical ordering of table rows according to the database implementation.
• Non-Clustered Index — separate index structure that points to the corresponding rows.
• Unique Index — prevents duplicate indexed key values.
• Composite Index — index containing multiple columns.

5. Clustered vs Non-Clustered Index

6. Indexes Are Not Free

Indexes improve reads, but they also consume storage and add work when indexed data is inserted, updated, or deleted.

flowchart LR
    W["INSERT / UPDATE / DELETE"] --> T["Table"]
    W --> I["Maintain Index"]

    I --> C["Additional Storage + Write Cost"]

Therefore, creating an index on every column is usually not a good design.

7. When Should You Create an Index?

Consider indexing columns frequently used in:

• WHERE conditions
• JOIN conditions
• ORDER BY
• GROUP BY where appropriate
• Frequently executed queries

flowchart LR
    Q["Frequently Used Query"] --> A["Analyze Query"]
    A --> C["Filter / Join / Sort Columns"]
    C --> I["Consider Index"]

8. Index Selectivity

An index is generally more useful when the indexed column can narrow the search to a relatively small portion of the table.

For example, an index on a unique email column is typically much more selective than an index on a column containing only two possible values.

9. Too Many Indexes

flowchart LR
    T["Table"] --> I1["Index 1"]
    T --> I2["Index 2"]
    T --> I3["Index 3"]
    T --> I4["Index 4"]
    T --> IN["Many More Indexes"]

    IN --> W["Higher Write / Storage Cost"]

The goal is not to maximize the number of indexes. The goal is to create indexes that support important query patterns.

Important Interview Questions

• What is a database index?
• Why does an index improve query performance?
• What happens if there is no suitable index?
• Clustered vs non-clustered index?
• Can a table have multiple indexes?
• Why shouldn't we create indexes on every column?
• How do indexes affect INSERT, UPDATE, and DELETE?
• What is index selectivity?
• Which columns would you consider indexing?
• How would you investigate whether an index is actually helping?

Key Takeaways

• Index → faster data lookup for supported query patterns.
• Without a suitable index → the database may perform a larger scan.
• Indexes consume storage and add write-maintenance cost.
• Don't blindly index every column.
• Choose indexes based on real query patterns and workload.

Conclusion

For interviews, remember: Index = Faster Reads, but Additional Storage and Write Cost.