Indexes

Overview

An Index is a B-Tree or Hash data structure created on table columns to accelerate data retrieval operations.

Learning Objectives

• Create indexes using CREATE INDEX.
• Compare Index Seeks ($O(\log N)$) vs. Table Scans ($O(N)$).
• Avoid over-indexing columns that degrade INSERT/UPDATE speed.

Detailed Concept Explanation

Indexes work like a book index. Instead of reading every page (Table Scan), the database jumps directly to the matching location (Index Seek).

Code Examples

SQL

-- Create B-Tree index on email column for fast lookups
CREATE INDEX idx_users_email ON users(email);

Summary

Indexes speed up WHERE, JOIN, and ORDER BY queries, but add slight overhead to write operations.