Composite Indexes

Introduction

A composite index covers multiple columns. Order matters — leftmost columns are usable on their own; later ones aren't (the 'leftmost prefix' rule).

Understanding the topic

Core concepts to understand:

• CREATE INDEX ON orders(user_id, status);
• Usable for queries on (user_id) or (user_id, status) — but not (status) alone.
• Match the index order to your most-frequent queries.
• Covering index: includes all columns the query reads.
• Bigger than single-column indexes — pick judiciously.

Syntax reference

Visual workflow / architecture:

Index (user_id, status)
✓ WHERE user_id=5
✓ WHERE user_id=5 AND status='paid'
✗ WHERE status='paid'  (misses leftmost)
✓ INCLUDE (amount)  → index-only scan

Real-world use

Postgres covering indexes (with INCLUDE) and MySQL/MariaDB clustered indexes drive sub-millisecond lookups for hot pages.

Best practices

• Order columns by selectivity & query order.
• Add INCLUDE for read-heavy hot queries.
• Don't duplicate prefixes across indexes.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Leftmost prefix rule.
• Q2. Covering vs composite index.
• Q3. Index order trade-offs.
• Q4. Index-only scan — what is it?
• Q5. When to use INCLUDE.