Indexes speed up queries on large tables. CREATE INDEX on columns in WHERE and JOIN clauses. Use EXPLAIN ANALYZE to check if indexes are being used. Drop unused indexes — they slow down writes without helping.
Creating Indexes
-- Default B-tree index
CREATE INDEX idx_users_email ON users(email);
-- Unique index
CREATE UNIQUE INDEX idx_users_phone ON users(phone);
-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Drop index
DROP INDEX idx_users_email;
-- See query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';