Mini project: optimise a deliberately bad schema/query set

This mini project optimises a deliberately bad schema and query set. The scenario: a legacy shop database grew without review. Pages time out, reports lock tables, and nobody wants to touch it. Your job is to diagnose, fix schema and indexes, rewrite queries, and prove improvement with EXPLAIN.

Starting schema (intentionally bad)

CREATE TABLE legacy_orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_email VARCHAR(255),
  customer_name VARCHAR(255),
  status VARCHAR(50),
  notes TEXT,
  placed_at DATETIME,
  total DECIMAL(10,2)
);

CREATE TABLE legacy_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT,
  product_name VARCHAR(255),
  sku VARCHAR(50),
  qty INT,
  price DECIMAL(10,2)
);

-- No foreign keys, no indexes beyond PK, duplicated customer data per order

Insert sample data – at least 50k orders and 200k line items. Use a script or repeated INSERT batches. Bad performance shows up with volume.

Bad queries to fix

Query 1 – dashboard pending orders

SELECT * FROM legacy_orders
WHERE status = 'pending'
ORDER BY placed_at DESC;

Run EXPLAIN. Add index on (status, placed_at). Consider dropping SELECT * if notes is huge.

Query 2 – order detail with items (N+1 in app)

SELECT * FROM legacy_orders WHERE id = 12345;
SELECT * FROM legacy_items WHERE order_id = 12345;

Acceptable as two queries with index on legacy_items(order_id). Better: one JOIN for list pages.

SELECT o.id, o.placed_at, o.total, i.product_name, i.qty, i.price
FROM legacy_orders o
INNER JOIN legacy_items i ON i.order_id = o.id
WHERE o.customer_email = 'alice@example.com';

Needs index on legacy_orders(customer_email) and legacy_items(order_id).

Query 3 – revenue report

SELECT DATE(placed_at) AS day, SUM(total) AS revenue
FROM legacy_orders
WHERE status IN ('paid', 'shipped')
  AND YEAR(placed_at) = 2026
GROUP BY DATE(placed_at);

Functions on placed_at block index use. Rewrite:

SELECT DATE(placed_at) AS day, SUM(total) AS revenue
FROM legacy_orders
WHERE status IN ('paid', 'shipped')
  AND placed_at >= '2026-01-01' AND placed_at < '2027-01-01'
GROUP BY DATE(placed_at);

Schema improvements

Normalise customers out of orders (optional but cleaner):

CREATE TABLE customers (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL
);

ALTER TABLE legacy_orders ADD COLUMN customer_id INT UNSIGNED;
-- Backfill customer_id from email, then drop customer_email/name when ready

ALTER TABLE legacy_items
ADD CONSTRAINT fk_items_order
FOREIGN KEY (order_id) REFERENCES legacy_orders(id);

Deliverables

• EXPLAIN before/after for each fixed query (screenshot or paste)
• List of indexes added with one-line justification
• At least one query rewritten to avoid function on indexed column
• One paragraph: what you would migrate next if this were production (customer table, partitioning old orders, etc.)

The final tutorial is a mini project: plan a safe migration for a breaking schema change.