DBA: Performance Tuning at Scale

Scaling up

Cloned the Data Modeling project's exact schema into its own database, seeded with the real rows plus 1,000,000 synthetic rows — generated set-based (a six-way cross join of ten digits, not a slow recursive loop), reusing the existing companies/locations/sources rather than inventing new lookup data. About 4 minutes end to end for the full million-row insert.

Finding a query worth tuning

The Data Modeling project's own writeup flagged the exact case that would eventually matter: a composite index would be worth it "if the real query patterns filter on both country and date range together" — written when the dataset was too small (1,392 rows) for that to pay off. At a million rows, it does.

Query: senior roles posted in a date window, most recent first — no existing index covered seniority.

Before adding an index: EXPLAIN ANALYZE reported 26.8 ms actual execution time, scanning 335,892 rows via the date index before filtering by seniority.

After CREATE INDEX idx_seniority_date_posted ON jobs (seniority, date_posted): 1.6 ms — a single index range scan satisfies both predicates directly. Roughly a 17x drop in the query plan's own reported execution time.

The benchmark had a real gotcha

The first timing attempt spawned a fresh database client process for every run — the process-spawn overhead (~70ms) completely swamped a query that only takes single-digit milliseconds, showing zero visible improvement and contradicting what EXPLAIN ANALYZE had just shown. Fixed by benchmarking inside a single session (a stored-procedure loop, 200 iterations, timed server-side): 745µs → 416µs, a real but smaller ~1.8x improvement — smaller because a warmed buffer pool narrows the gap between "good index" and "scan-then-filter" once the data's already cached in memory. Both numbers are true; they answer different questions about cold vs. warm cache performance.

Backup & restore — tested, not just documented

mysqldump --single-transaction --routines --triggers ... | gzip > backup.sql.gz

--single-transaction takes a consistent snapshot without locking tables — safe against a database the ingestion pipeline is actively writing to. Restored into a fresh test database and verified row-by-row:

table	original	restored
jobs	1,567	1,567
companies	495	495
locations	381	381
sources	5	5

Exact match. The 1,567-row database compresses to 72K.