I remember this one clearly. We had an endpoint that was getting slower by the day. At first, it took 200ms. Then 500ms. Then over 2 seconds. Users started complaining.
The Symptoms
The API was simple — fetch a list of orders with user details. Nothing fancy. But as the database grew, response times exploded. I started checking the usual suspects:
- Server CPU? Normal.
- Memory? Fine.
- Network latency? Not the issue.
The Wrong Path
I spent hours looking at the application code. Added caching. Refactored the query builder. Even considered switching to a different ORM. Nothing worked.
Then a senior dev asked me a simple question: “Did you check the EXPLAIN output?”
I hadn’t. I was too busy looking at the code.
The Real Problem
When I ran EXPLAIN on the query, I saw it — a full table scan. The query was filtering by user_id on the orders table, but there was no index on that column. With 2 million rows, the database was scanning every single record.
The Fix
One line. That’s all it took.
CREATE INDEX idx_orders_user_id ON orders(user_id);Query time dropped from 2.3 seconds to 12ms. Twelve. Milliseconds.
What I Learned
- Check the database first. Slow queries are usually a data layer problem, not an app problem.
- EXPLAIN is your friend. It shows exactly what the database is doing.
- Indexes matter more as data grows. What works with 1,000 records fails at 1,000,000.
Now whenever I build a new endpoint, I think about the queries behind it. Filter columns, join keys, foreign IDs — if they’re queried often, they probably need an index.
That 6-hour debugging session saved me countless more hours down the road. Sometimes the simplest fix takes the longest to find.