Indexing for Speed

Why Queries Get Slow

Without an index, the database scans every row to find matches (a full table scan). An index is an ordered structure that lets it jump straight to the answers.

Creating an Index

CREATE INDEX idx_customers_city ON customers(city);
CREATE UNIQUE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_name_lower ON customers(LOWER(name));

What to Index

• Columns that appear in WHERE and JOIN conditions.
• Columns used with ORDER BY for sorted reads.
• Foreign key columns (the database often does this for you).

Indexes Have Costs

Every index slows down INSERT, UPDATE and DELETE because the index must be maintained too, and it uses disk. Index what your queries need - not every column.

Checking the Plan

-- MySQL / MariaDB
EXPLAIN SELECT * FROM customers WHERE email = 'a@b.com';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM customers WHERE email = 'a@b.com';

Look for "Index Scan" instead of "Seq Scan" to confirm the index is used.

Key Points

• Indexes turn table scans into tree lookups.
• Index WHERE, JOIN and ORDER BY columns.
• They trade write speed and disk for read speed.
• EXPLAIN shows whether the index is actually used.