One missing index turned a 40ms query into a 12-second one

The query had run fine for two years. It joined orders to customers on a foreign key, filtered by date range, and came back in under 40 milliseconds every time anyone checked it, which nobody had reason to do, because it wasn't slow. Then a support ticket came in about a report timing out, and the same query, unchanged, was taking 12 seconds. Nothing about the query had changed. What had changed was the table it joined against, which had grown from a few hundred thousand rows to just over 2 million over those two years, gradually enough that no single deploy looked like the moment it broke. The foreign key column on the customer side had never had an index. It didn't need one at 200,000 rows, a full scan of a table that size is fast enough that the missing index costs nothing anyone would notice. At 2 million rows, the same scan is a different kind of operation entirely, and the query planner had no faster path to fall back on because there wasn't one to fall back to. The part that made this take longer to diagnose than it should have was that the execution plan looked reasonable at a glance, a join, a filter, nothing exotic. It was only clear once someone ran EXPLAIN ANALYZE and saw a sequential scan against the customer table where an index scan should have been, at which point the missing index became obvious in a way it never had been from reading the schema itself. A missing index doesn't announce itself in code review, it's the absence of something, and absences don't get flagged unless someone is specifically looking for them. Adding the index took a few minutes and, on a table that size, a maintenance window to build without locking writes for the duration. Query time dropped back to single-digit milliseconds immediately afterward. The fix wasn't clever, it was the most standard advice in relational database work, index your foreign keys, and it still took a production incident to surface a gap that had been sitting there since the table was created. Table growth is slow and continuous, which is exactly why this kind of gap survives so long. Nothing about a 2-million-row table looks alarming from the outside, and a query that's always been fast doesn't get re-audited just because the data underneath it kept growing. If you have foreign keys on tables that started small and never got a second look, it's worth checking now, before the row count picks the moment for you. More like this? I write short production war stories like this one, plus a deep-dive architecture series. Subscribe here if you want both in your inbox. Recommended Resources for Java & Spring Boot Engineers If you are preparing for Senior/Lead Java interviews or looking to solidify your Spring Boot & Architecture skills, check out these highly-rated resources from the Javarevisited publication (Use promo code friends20 for an exclusive 20% discount automatically applied at checkout): - Grokking the Java Interview Prepare for core Java, concurrency, JVM internals, and design pattern questions. Get Full Book (Paid) | Download Free Sample Copy - Grokking the Spring Boot Interview Master Spring Core, Auto-configuration, Spring Data JPA, Security, and Microservices. Get Full Book (Paid) | Download Free Sample Copy - Grokking the SQL Interview Deep-dive into query optimization, indexing, joins, and complex SQL window functions. Get Full Book (Paid) | Download Free Sample Copy - The Complete Java + Spring + SQL Interview Bundle Get all three interview guides in a single heavily discounted package. Get the Ultimate Interview Bundle - Spring Professional Certification Practice Questions (250+ Questions) Validating your skills? Practice with real exam-style questions before taking the Spring Professional certification. Get the Spring Professional Questions | Download Free Sample Copy Top comments (0)