Indexes

Lesson content

Indexes An index is an additional lookup structure that can speed reads at the cost of storage and write work. Example CREATE INDEX idx_orders_customer ON orders(customer_id); Key point Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use. Real-life example An orders query that was fast with a thousand rows becomes slow at ten million rows. The team must measure the plan and improve it without changing the result. Advanced example CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_at DESC); EXPLAIN SELECT id, total, ordered_at FROM orders WHERE customer_id = 42 ORDER BY ordered_at DESC LIMIT 20; Expected result The execution plan shows a measured reduction in unnecessary scanning or sorting without changing query results. Production check Test with empty, duplicate, null, and boundary values. Use a transaction for related writes. Inspect the execution plan before adding an index. Use parameterized queries for application input. Continue with the PicoStore database This lesson reuses picostore . Relevant tables: products, orders . Keep the starter rows from the Introduction lesson so results remain comparable. Another practical example CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_at DESC); EXPLAIN SELECT order_id, total FROM orders WHERE customer_id = 1 ORDER BY ordered_at DESC LIMIT 20; Check the result Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct. Easy example Start with a small customer table and retrieve active customers in a predictable order. SELECT customer_id, name, email FROM customers WHERE status = 'active' ORDER BY name; How to verify the easy example Run it with representative input. Confirm the expected output. Try one missing, invalid, or boundary value. Advanced example Use a CTE and a window function to rank customer revenue while keeping the query readable and testable. WITH customer_revenue AS ( SELECT customer_id, SUM(total_amount) AS revenue FROM orders WHERE order_status = 'completed' GROUP BY customer_id ) SELECT customer_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM customer_revenue ORDER BY revenue_rank, customer_id; Advanced review Explain the tradeoffs and assumptions. Test failure, scale, security, and recovery behavior. Capture evidence from tests, execution plans, logs, or review output. Additional practical guidance Indexes: MySQL and PostgreSQL Index and optimizer behavior is vendor-specific. Compare EXPLAIN output on the actual MySQL or PostgreSQL version and measure with production-shaped data before keeping an index. Required verification Run the simple case. Test a NULL, duplicate, empty, or boundary case where relevant. Confirm the affected rows or query result. Use EXPLAIN for performance-sensitive queries.