It was 2 AM when my phone started buzzing. Not once, not twice — it went full siren mode. Our main API was timing out, and the error messages were everywhere.
Here’s what happened.
The Setup
We had a simple users table. Nothing fancy — just the usual stuff: id, email, name, created_at. We needed to fetch users by email for authentication. Pretty standard stuff.
The query looked like this:
SELECT * FROM users WHERE email = '[email protected]';
It worked fine in development. It worked fine in staging. For months, it worked fine in production.
Then It Stopped Working
Our user base grew. We hit maybe 50,000 users. And suddenly, that simple query started taking 30 seconds. Then 60 seconds. Then timeouts.
I SSH’d into the server, ran the query locally, and watched my terminal hang. That’s when I knew we were in trouble.
The Fix Was Embarrassingly Simple
I ran EXPLAIN on the query and saw the problem instantly — full table scan. There was no index on the email column.
One line of SQL later:
CREATE INDEX idx_users_email ON users(email);
Query time went from 30+ seconds to milliseconds. The whole incident lasted about 45 minutes. We lost some users during that time. Not a great night.
What I Learned
- Indexes aren’t optional. If you’re querying a column frequently, index it. Don’t wait until production breaks.
- Development data ≠ production data. That query with 100 rows works fine. With 500,000 rows, it’s a different story.
- Monitor slow queries. We set up query logging after that. Now we catch these things before they become incidents.
Now whenever I create a new table, I think about the queries that will run against it. What will we filter by? What will we join on? Then I add indexes upfront.
It’s a simple lesson, but it’s the kind of mistake that teaches you more than any tutorial ever could.