Lumina

Manuals / SQL / Ch 10

D · PerformanceAdvanced40 min read

10. Indexes & EXPLAIN

SQL · 48 pages source format

Indexes speed reads, slow writes. B-tree default. EXPLAIN shows query plan. Index columns in WHERE and JOIN.

What you'll learn

  • CREATE INDEX
  • EXPLAIN / EXPLAIN ANALYZE
  • When to index
  • Covering indexes lite

Why indexes

Without index: full table scan. With index on customer_id: fast lookup for JOINs and WHERE customer_id = ?.

Do this now

Read Use The Index, Luke — chapter 1. Note one insight.

Clear?

EXPLAIN

EXPLAIN SELECT ... shows plan. Seq Scan = full scan. Index Scan = using index. Compare before/after index.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Do this now

EXPLAIN a slow query. CREATE INDEX. EXPLAIN again. Compare.

Clear?

Index discipline

Index foreign keys and frequent WHERE columns. Do not index every column — writes get slower.

Do this now

Hypothesis: which column to index on your sandbox? Test with EXPLAIN.

Clear?

Checklist