SQL Lab — Index Optimisation (Hard)

🖥️

SQL Terminal

⚡

Query Profiler

📊

Schema

📌 OBJECTIVES — NO HINTS

○Identify all slow queries (>2s)

○Analyse with EXPLAIN ANALYZE

○Remove redundant indexes

○Create optimal covering indexes

○Use index hints to force plans

○All 4 queries under 80ms

○Write optimisation report

0 / 7 complete

SQL TERMINAL — ecommerce_db (12,847,231 rows)

MySQL 8.0 — ecommerce_db Tables: orders (12M), products (840K), customers (2.1M), order_items (38M) ⚠ ALERT: 4 dashboard queries timing out during peak traffic. SLA breach imminent. No hints provided — diagnose and optimise independently. ──────────────────────────────

mysql>

QUERY PROFILER — 4 problem queries

Q1: Revenue by category (last 30 days)8,420ms

Q2: Customer order history lookup6,180ms

Q3: Top products by region + date range11,340ms

Q4: Abandoned cart aggregation4,890ms

Target: all queries under 80ms ✗

SCHEMA — ecommerce_db

CURRENT INDEXES

orders: PRIMARY(id), idx_status(status), idx_created(created_at), idx_customer(customer_id), idx_status_created(status,created_at) — ⚠ 2 redundant

products: PRIMARY(id), idx_category(category_id), idx_name(name), idx_price(price) — ⚠ idx_name rarely used

order_items: PRIMARY(id), idx_order(order_id), idx_product(product_id) — ⚠ missing covering index

customers: PRIMARY(id), idx_email(email) — ⚠ missing region+created composite

PROBLEM QUERIES

Q1: SELECT p.category, SUM(oi.quantity * oi.unit_price) FROM order_items oi JOIN orders o ON oi.order_id=o.id JOIN products p ON oi.product_id=p.id WHERE o.created_at > DATE_SUB(NOW(),INTERVAL 30 DAY) GROUP BY p.category

Q2: SELECT * FROM orders WHERE customer_id=? AND status='completed' ORDER BY created_at DESC LIMIT 20

Q3: SELECT p.name, c.region, SUM(oi.quantity) FROM order_items oi JOIN orders o ON oi.order_id=o.id JOIN customers c ON o.customer_id=c.id JOIN products p ON oi.product_id=p.id WHERE o.created_at BETWEEN ? AND ? GROUP BY p.name, c.region ORDER BY 3 DESC LIMIT 50

Q4: SELECT customer_id, COUNT(*) FROM orders WHERE status='abandoned' AND created_at > DATE_SUB(NOW(),INTERVAL 7 DAY) GROUP BY customer_id HAVING COUNT(*)>1

MISSION BRIEFING

INDEX OPTIMISATION — NEXACOMMERCE · HARD ⭐⭐⭐

SCENARIO

NexaCommerce's analytics dashboard is timing out during Black Friday peak traffic. Four queries hit a 12M-row orders table and are breaching the 80ms SLA. The previous DBA left a mess of redundant and missing indexes. No hints — diagnose and fix it yourself.

WHAT TO DO

Run SHOW INDEX FROM orders/order_items/customers/products
Run EXPLAIN ANALYZE on each problem query
Identify full table scans, filesorts, and redundant indexes
Drop redundant indexes to reduce write overhead
Create covering indexes that satisfy the WHERE + GROUP BY + ORDER BY of each query in a single index scan
Use USE INDEX(idx_name) or FORCE INDEX where MySQL chooses wrong
Verify all 4 queries are under 80ms in the Profiler

HARD MODE RULES

No syntax templates provided. You must reason from first principles about which columns belong in the index and in what order. The covering index for Q1 is non-obvious — the key insight is the join order and which table drives the scan.

COMMANDS AVAILABLE

SHOW INDEX FROM <table>
EXPLAIN ANALYZE SELECT ...
CREATE INDEX idx_name ON table(col1, col2, col3)
DROP INDEX idx_name ON table
SELECT ... FROM t USE INDEX(idx_name) WHERE ...
SHOW PROFILE FOR QUERY 1
SELECT * FROM sys.schema_redundant_indexes

Display Mode