Slow Database Interview Question

Ajouté : 2026-05-06

[00:00:00]Let's start with another DevOps interview question. A developer writes a query that joins four tables. It takes 30 seconds to return results.

[00:00:07]Their solution?

[00:00:09]Upgrade the database to a bigger instance.

[00:00:11]Is that the right move?

[00:00:13]Almost never.

[00:00:14]And this is the question where they're testing if you actually understand databases or if you just throw money at problems.

[00:00:21]The thing is, a 30-second query is almost always a code problem, not a hardware problem.

[00:00:26]If you upgrade from 8 gigs of RAM to 32, that query might go from 30 seconds to 25 seconds.

[00:00:33]You burned $400 a month to save 5 seconds. The query is still terrible.

[00:00:39]So, what's actually going on?

[00:00:41]Let me walk you through the usual suspects.

[00:00:43]Missing indexes.

[00:00:45]This is the number one cause. The database is scanning millions of rows when it should be reading 50.

[00:00:51]Think of it like searching a phone book page by page instead of using the alphabet tabs.

[00:00:56]A single index on the right column can turn 30 seconds into 50 milliseconds.

[00:01:01]I've seen it happen more times than I can count.

[00:01:03]The N + 1 problem.

[00:01:06]Your code fetches 100 users, then runs a separate query for each user's orders.

[00:01:10]That's 101 database round trips when one join would do it.

[00:01:15]The scary part? You won't see this in the query itself. It's hiding in your application code firing the same query in a loop.

[00:01:22]Bad joins.

[00:01:24]Four-table joins aren't inherently slow, but joining on non-indexed columns or pulling back every column with select star when you need three fields, that's where it breaks down.

[00:01:35]So, here's what you actually do.

[00:01:37]Step one, run explain analyze. This is non-negotiable. It shows you exactly what the database is doing. Full table scans, ignored indexes, temporary tables.

[00:01:48]You can't fix what you can't see.

[00:01:51]Step two, add the right indexes.

[00:01:54]Not every column. That actually makes writes slower.

[00:01:57]Target your where clauses, join conditions, and order by columns.

[00:02:01]Composite indexes on the columns you actually filter by.

[00:02:05]And step three, fix the application layer.

[00:02:08]Eager loading instead of lazy loading.

[00:02:11]Batch queries instead of loops.

[00:02:13]And if you genuinely need heavy analytics across millions of rows, that's when you add a read replica.

[00:02:19]Not to fix the query, but to protect your primary database from the load.

[00:02:23]The pattern is always the same.

[00:02:25]Understand the problem, then fix it.

[00:02:28]Throwing hardware at a bad query is like putting a bigger engine in a car with square wheels.