Indexes and Query Performance

-- Create an index:
CREATE INDEX idx_emp_dept ON employees(department_id);

-- Now this query is fast:
SELECT * FROM employees WHERE department_id = 5;

An index is like a book's index — instead of scanning every page (full table scan), the database jumps directly to the right rows. Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE).

-- Index helps SELECT:
SELECT * FROM orders WHERE customer_id = 100; -- Fast with index

-- Index slows INSERT/UPDATE/DELETE:
INSERT INTO orders VALUES (...); -- Slower — must update all indexes
UPDATE orders SET status = 'shipped' WHERE id = 1; -- Slower

Every index must be updated on every write. Tables with many indexes have fast reads but slow writes. High-write tables (logs, events) should have minimal indexes.

-- Good for indexing:
-- 1. Columns in WHERE clauses
SELECT * FROM orders WHERE status = 'pending';

-- 2. Columns used in JOINs
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

-- 3. Columns in ORDER BY for large tables
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

-- Bad for indexing:
-- Boolean columns with 2 values (low cardinality)
-- Columns rarely used in queries

High-cardinality columns (many unique values like email, order_id) benefit most from indexes. Low-cardinality columns (gender, status with 2-3 values) benefit less.

-- 1. Full table scan (no index on WHERE column):
SELECT * FROM orders WHERE notes LIKE '%urgent%'; -- Cannot use index

-- 2. SELECT * (fetches unnecessary columns):
SELECT * FROM orders; -- vs SELECT id, amount FROM orders;

-- 3. N+1 queries (solved by JOINs):
-- Bad: loop query per customer in app code
-- Good: one JOIN query for all customers

-- 4. Missing JOIN condition (Cartesian product):
SELECT * FROM a, b; -- Returns a.rows x b.rows!

These are the top causes of slow SQL in practice. LIKE '%value%' (leading wildcard) never uses an index. SELECT * transfers unnecessary data. Always check query execution plan with EXPLAIN.

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

-- Output shows:
-- type: ALL = full scan (bad), ref/eq_ref = index used (good)
-- rows: estimated rows scanned
-- key: which index was used (NULL = no index)
-- Extra: 'Using filesort' or 'Using temporary' = potential slow

EXPLAIN shows the query execution plan without running the query. Use it to identify full table scans, missing indexes, and expensive sorts. Essential skill for production SQL work.