Measure Before You Optimize

The Baseline Principle

Optimizing without measuring is guessing. Before making any changes to indexes or query structure I ran every analytical query against the unoptimized database and recorded exact execution times. This gives a honest baseline to measure improvements against — and it forces you to understand what the optimizer is actually doing before you tell it to do something different.

Six Queries, Two Problems

I designed six analytical queries covering the main research questions — cancer trends, air quality correlations, water violation counts, CAFO facility proximity, and smoking prevalence. Four queries performed acceptably. Two did not — Q4 at 9 minutes 42 seconds and Q5 at 4,658 ms. Both were diagnosed with EXPLAIN before any fixes were attempted.

Execution Times

All six queries were run in DataGrip against the unoptimized database with default MySQL 8.0 configuration and no custom indexes beyond the primary keys and foreign key indexes created during schema design.

Q4 — The 9-Minute Query

EXPLAIN revealed that Q4 was performing a full nested-loop scan through all 15.3 million rows in fact_water_violations. The is_health_based_ind = 'Y' filter was being applied only after the join to dim_water_system and dim_state — meaning MySQL was joining the entire 15.3M row table before filtering it down to the health-based subset. The fix required a composite index on (is_health_based_ind, pwsid) plus a CTE rewrite to pre-filter before joining.

Screenshot — Q4 baseline EXPLAIN output

Q4 EXPLAIN — type: ALL on fact_water_violations, 15.3M rows, is_health_based_ind filter applied after the join

Q5 — The 4.6-Second CAFO Query

EXPLAIN showed Q5 was scanning all 1.18 million rows in dim_cafo_facility despite an existing state_id index. The optimizer ignored it because impaired_waters had very low cardinality — only two distinct values. With no index covering impaired_waters, the optimizer chose a full scan over the existing index. The fix required a composite index on (impaired_waters, state_id) with the correct column order.

Before vs After — The Targets

Phase 3 established the exact targets for Phase 4. Every optimization decision in Phase 4 traces back to what EXPLAIN revealed here. The two problem queries had clear, diagnosable root causes — and the diagnosis came before any fix was attempted.