SQL Lab — Index Tuning

🖥️

SQL Terminal

⚡

Query Profiler

🔍

EXPLAIN Output

📌 OBJECTIVES

○Run EXPLAIN on slow query

○Identify full table scan

○Create index on amount column

○Create composite index

○Verify <200ms execution time

○Document findings

0 / 6 complete

SQL TERMINAL — analytics_db.transactions

MySQL 8.0 — analytics_db Table: transactions (500,000 rows) ⚠ Dashboard queries timing out. Investigate and optimise. ──────────────────────────────────────

mysql>

QUERY PROFILER — execution time

Run queries to see execution times. Target: under 200ms.

SELECT with full table scan (no index)4,200ms

Scanning all 500,000 rows — type: ALL

SELECT with single-column index—

Create an index to unlock this result

SELECT with composite index—

Create a composite index to unlock this result

EXPLAIN OUTPUT

HOW TO READ EXPLAIN

type: ALL — full table scan (bad)

type: ref — index lookup (good)

type: range — index range scan (good)

rows — estimated rows examined

key — which index was used (NULL = none)

Extra — additional info about the plan

Run EXPLAIN SELECT ... to see the query plan here.

MISSION BRIEFING

INDEX TUNING — APEX ANALYTICS

SCENARIO

The Apex Analytics dashboard has been timing out. The culprit is a query on the transactions table — 500,000 rows, no indexes. Your job is to diagnose the query plan with EXPLAIN, identify the bottleneck, and create the right indexes to bring execution time under 200ms.

THE SLOW QUERY

SELECT * FROM transactions WHERE amount > 1000 AND status = 'completed' ORDER BY created_at DESC LIMIT 50;

Currently taking ~4,200ms. Unacceptable for a dashboard.

COMMANDS

EXPLAIN SELECT * FROM transactions WHERE amount > 1000;
SHOW INDEX FROM transactions;
CREATE INDEX idx_amount ON transactions(amount);
CREATE INDEX idx_status_amount ON transactions(status, amount);
CREATE INDEX idx_status_amount_date ON transactions(status, amount, created_at);
EXPLAIN ANALYZE SELECT ...;
DROP INDEX idx_name ON transactions;

TIP

A composite index on (status, amount) will be more effective than individual indexes because MySQL can use it for both the WHERE clause and avoid a filesort. Try both and compare the profiler results.

Display Mode