Optimizing SQL with Query Statistics

This post details the optimization of a slow SQL query within PlanetScale's database backup feature. By analyzing query statistics and MySQL explain plans, the team identified that a full table scan was being performed instead of using an existing composite index. Through analysis of data selectivity (percentage of soft-deleted rows vs. expired rows), they determined that a reordered index `(deleted_at, expires_at, data_deleted_at)` would be more effective. This change reduced the query's average runtime from 719ms to under 20ms.