DEV Community

Matsu
Matsu

Posted on

"The API Is Slow, Fix It!" – What I Learned About `EXPLAIN` and Indexing

#sql #index #performance

During a technical interview, the situation was something like this:

“Your manager tells you: the API is too slow. Fix it!”

So I started walking through what I’d do — checking logs, measuring response time, analyzing the stack. Eventually, we narrowed it down to the database, suspecting a slow query.

I mentioned I’d try to optimize it based on “feeling” and maybe throw in some indexes. Then the interviewer dropped a simple but powerful tip:

“Have you tried running it with EXPLAIN?”

That’s when I learned something new and honestly, it changed the way I look at query performance.

What Does EXPLAIN Actually Show?

When you write a query, the database doesn’t just run it blindly, it builds an execution plan. And EXPLAIN reveals exactly how it plans to execute that query.

Think of it like saying: “Show me how you're going to fetch these results before you actually do it.”

With it, you can discover:

  • If it’s doing a full table scan;
  • If your indexes are being used or totally ignored;
  • What kind of join is being applied (Nested Loop, Hash Join, etc.);
  • How many rows it thinks it will read;
  • And how “expensive” the query is, cost-wise.

It’s simple to run:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
Enter fullscreen mode Exit fullscreen mode

Why Not Add Indexes Everywhere?

Another key part of the interview was when we discussed indexing. I first said, "maybe because of storage cost?" — but the better answer was:

"Indexes speed up reads, but make writes more expensive."

Every time you run an INSERT, UPDATE, or DELETE, the database also has to update every related index. So the trade-off is:

  • Too many indexes → fast reads, but slow writes.
  • Too few indexes → fast writes, but slow reads.

That’s why indexing is a design choice, not a default solution. And again, EXPLAIN helps you check if your index is actually being used or if it’s just sitting there eating up space.

What I Took from That Interview

  • EXPLAIN isn’t optional, it’s your ally for understanding performance;
  • Indexing is powerful, but it comes at a cost. Use it with intention;
  • And most of all: interviews aren’t just tests. Sometimes, they’re great learning moments.

Console You Later!

Top comments (0)