Skip to main content

Implementing Indexes for Performance

The marketplace database starts small, but imagine thousands of products and years of order history. Without indexes, every query would scan entire tables. Indexes provide shortcuts that keep reads fast while you scale.

Learning Goals

  • Identify columns in the schema that benefit from indexing
  • Create single-column and composite indexes
  • Understand how indexes impact queries and write performance
  • Practice measuring index usage with EXPLAIN

Where Indexes Help​

Good candidates include:

  • Columns used in JOIN conditions (customer_id, order_id, product_id, category_id)
  • Columns frequently searched (sku, email)
  • Columns used for sorting or filtering by range (orders.order_date, orders.order_total)

Remember: every PRIMARY KEY automatically has an index; you only need to add extra ones when queries rely on other columns.

Creating Indexes​

Index the order item join column
CREATE INDEX idx_order_item_order
ON order_item (order_id);

Now queries that fetch an order's items avoid scanning the entire order_item table:

SELECT oi.quantity, oi.unit_price, p.sku, p.product_name
FROM order_item AS oi
JOIN product AS p ON p.product_id = oi.product_id
WHERE oi.order_id = 3;

Composite Index​

Composite indexes speed up queries that filter on multiple columns in the same order as the index definition.

Prevent duplicate lookups by order/product
CREATE UNIQUE INDEX idx_order_item_order_product
ON order_item (order_id, product_id);

MySQL automatically indexes UNIQUE constraints, but an explicit name can still make troubleshooting easier.

Covering Range Queries​

Index order dates for faster reporting
CREATE INDEX idx_orders_order_date
ON orders (order_date);

SELECT order_date, COUNT(*) AS total
FROM orders
GROUP BY order_date
ORDER BY order_date DESC;

Measuring Impact with EXPLAIN​

Use EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) to see whether the optimizer uses your index.

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 5 AND order_date LIKE '2025-03%';

Look for the index name in the key column of the EXPLAIN output.

When Not to Index​

  • Columns with very few distinct values (status, which is mostly Pending, Shipped, etc.) rarely benefit.
  • Tables that change constantly might suffer because each insert/update/delete must update every index.
  • Avoid redundant indexes (e.g., both (order_id) and (order_id, product_id) when you only query on order_id).

Maintenance Tips​

  • Review indexes alongside query patterns; remove unused ones to cut write overhead.
  • After bulk imports, some databases need statistics refreshed (ANALYZE TABLE or VACUUM ANALYZE).
  • Document why each index exists so future maintainers know when it is safe to drop.

Summary​

  • Indexes speed up read-heavy workloads by avoiding full table scans.
  • Target join keys, lookup columns, and ordering columns.
  • Composite indexes matter when queries filter on multiple fields in sequence.
  • Monitor performance with EXPLAIN and keep only the indexes you need.

Quiz​

Show quiz

  1. Which column is the best candidate for an index if you often query SELECT * FROM product WHERE sku = ??
    A) product_name
    B) sku
    C) price
    D) category_id

  2. What is a downside of creating too many indexes on order_item?
    A) Queries get slower
    B) Inserts and updates must maintain each index, slowing writes
    C) Foreign keys stop working
    D) Order statuses become less accurate

  3. How can you verify that a query uses idx_order_item_order?
    A) Check the database logs
    B) Run SHOW COLUMNS FROM order_item
    C) Run EXPLAIN on the query
    D) It is automatic; no verification is needed

  4. True or False: An index on (order_id, product_id) can also speed up queries that filter only on product_id.
    A) True
    B) False

Answers

  1. B
  2. B
  3. C
  4. B