From 12 seconds to under 0.1
Summary tables over 18 million rows from a legacy DBF scheduling system.
The problem
The incident dashboards needed kilometers driven and staff on duty per line, vehicle and day. That data lived in a schedule table imported from an old DBF system, with more than 18 million rows and fields stored as text. Every query had to convert and aggregate everything again.
What I did
I designed two summary tables computed once: kilometers per line, vehicle and day (5.9 million rows) and staff per line, vehicle and day (11.5 million). The load cleans the data: safe type conversion, discarding invalid rows and filtering years. Both tables are keyed and indexed by line, vehicle and date.
Result
Queries equivalent to the dashboard's went from about 12 seconds to under 0.1: over 100 times faster. Measured on the same database, three runs per query.