Indexes & Performance

CREATE INDEX index_name ON table (column);

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Speeds up queries filtering or joining on orders.user_id

Note Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index must be maintained. Only index columns that appear frequently in WHERE, JOIN, and ORDER BY clauses.

CREATE UNIQUE INDEX index_name ON table (column);

CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Enforces uniqueness and speeds up lookups by email

Note A unique index enforces a constraint and provides index performance in one operation. It is functionally similar to a UNIQUE constraint. In PostgreSQL, UNIQUE constraints are implemented as unique indexes internally.

CREATE INDEX index_name ON table (col1, col2);

CREATE INDEX idx_orders_user_date
  ON orders (user_id, order_date DESC);

-- Optimizes queries filtering by user_id AND order_date

Note Column order matters enormously. The index is useful for queries on (user_id), (user_id, order_date), but NOT for (order_date) alone. Put the most selective or most-filtered column first. This is the leftmost prefix rule.

-- PostgreSQL
CREATE INDEX index_name ON table (column) WHERE condition;

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- Smaller, faster index that only covers pending orders

Note Partial indexes are smaller and faster because they only include rows matching the WHERE condition. PostgreSQL supports them natively. MySQL does not - use a generated column with an index as a workaround.

-- PostgreSQL 11+
CREATE INDEX index_name ON table (col1) INCLUDE (col2, col3);

CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (total_amount, status);

-- Index-only scan possible for queries selecting total_amount and status filtered by user_id

Note A covering index includes all columns a query needs, enabling an index-only scan without touching the table. INCLUDE columns are stored in the index but not used for searching or sorting.

-- PostgreSQL
EXPLAIN ANALYZE SELECT ...;
-- MySQL
EXPLAIN SELECT ...;

EXPLAIN ANALYZE
SELECT u.first_name, COUNT(*) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.is_active = true
GROUP BY u.first_name;

-- Shows execution plan with actual timing and row counts
-- Look for: Seq Scan (bad on big tables), Index Scan (good), Hash Join vs Nested Loop

Note EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings. Use EXPLAIN (FORMAT JSON) in PostgreSQL for machine-parseable output. Never run EXPLAIN ANALYZE on destructive queries (DELETE/UPDATE) without wrapping in a transaction and rolling back.

-- Index when:
-- 1. Column is in WHERE frequently
-- 2. Column is in JOIN conditions
-- 3. Column is in ORDER BY
-- 4. Column has high cardinality (many unique values)

-- Skip indexing when:
-- 1. Table is small (< few thousand rows)
-- 2. Column has low cardinality (e.g., boolean)
-- 3. Table has heavy write load
-- 4. Column is rarely queried

-- Good: frequently filtered, high cardinality
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Questionable: boolean column, low cardinality
-- CREATE INDEX idx_users_active ON users (is_active);
-- Better as a partial index:
CREATE INDEX idx_users_active ON users (id) WHERE is_active = true;

-- Indexing strategy depends on query patterns and data distribution

Note Use EXPLAIN to verify indexes are being used. The optimizer may ignore an index if it estimates a sequential scan is faster (e.g., when selecting most of the table). Regularly check for unused indexes and remove them.

-- PostgreSQL
DROP INDEX index_name;
DROP INDEX CONCURRENTLY index_name;
-- MySQL
DROP INDEX index_name ON table_name;

-- PostgreSQL: non-blocking drop
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_old_status;

-- Index removed; writes become slightly faster, reads may slow down

Note In PostgreSQL, DROP INDEX CONCURRENTLY avoids locking the table during removal but cannot run inside a transaction. MySQL always requires specifying the table name. Remove unused indexes to save storage and speed up writes.

CREATE INDEX index_name ON table (expression);

CREATE INDEX idx_users_email_lower
  ON users (LOWER(email));

-- Query that benefits:
-- SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Speeds up case-insensitive email lookups

Note The query WHERE clause must use the exact same expression as the index. WHERE LOWER(email) = 'x' uses the index; WHERE email = 'x' does not. PostgreSQL and MySQL 8.0+ support expression indexes.