How a query runs
When you write a SELECT, you describe WHAT you want — 'give me the users in Pune' — not HOW to find them. So what happens after you hit enter? The database turns your sentence into a PLAN: a little recipe of steps like 'use the index on city to find matching rows, then filter, then sort.' It even compares a few different recipes and picks the cheapest one. That's the quiet genius of SQL: you say what, the database picks how. Let's watch a query travel from text to answer.
You say WHAT; the database decides HOW
SQL is DECLARATIVE. That word just means you describe the result you want, not the steps to get it. You write 'SELECT name FROM users WHERE city = "Pune"' — you never say 'first check row 1, then row 2…' You only state the goal. That leaves the HOW up to the database. And there are usually several ways to get the same answer: it could read every row and keep the Pune ones, OR if there's an index on city, it could jump straight to the Pune rows. Same result, very different speed. Deciding which path to take is the database's job — and it's the whole story of how a query runs.
Step by step: from text to a PLAN
First the database PARSES your query — it reads the text and checks it makes sense (real table? real columns? valid grammar?). If you typed nonsense, this is where it complains. Then it PLANS. This is the important step. The database looks at the choices — scan the whole table, or use an index? — and builds a recipe of steps. A typical plan: 'use the index on city to find the matching rows → filter out any that don't fit the rest of the WHERE → join in the other table if needed → sort the results → hand them back.' Once the plan is chosen, the database simply runs those steps in order and returns your rows. The answer is the same whichever plan it picked; the plan only decides how fast you get it.
The query planner: an optimizer that estimates cost
The piece that builds and chooses the plan is called the QUERY PLANNER (or optimizer). Think of it like a maps app picking a route: there are several ways to your destination, and it estimates which is fastest before you drive. How does it estimate? The database keeps rough statistics about each table — how many rows, how many are unique, which columns have indexes. Using those, the planner guesses the COST of each possible plan: 'a full scan reads a million rows; using the city index reads maybe fifty.' It picks the cheapest guess. This is why declarative SQL is so powerful: because you only said WHAT you want, the database is free to choose the smartest HOW — and it can even change its mind tomorrow when the table is bigger or you add a new index, all without you rewriting a thing.
EXPLAIN: ask the database to show its plan
Here's a friendly secret: you can ask the database to TELL you the plan it would use, before it runs. You put the word EXPLAIN in front of your query — 'EXPLAIN SELECT …' — and instead of the rows, it prints the recipe: 'I would use the index on city' or 'I would scan the whole table.' This is how real engineers debug slow queries. If a query is crawling, they run EXPLAIN and often find the database chose a full scan because the column they're filtering on has no index. They add the index, run EXPLAIN again, and now it says 'use the index' — query fixed. You don't need EXPLAIN to USE a database, but knowing it exists tells you the magic isn't magic: the database is making a choice, and you can peek at it.
It's a tradeoff
| Option | 👍 Pro | 👎 Con |
|---|---|---|
| Let the planner use an index (when one fits) | The plan jumps straight to the matching rows — fast even on a giant table. You wrote the same simple SQL; the database found the shortcut. | Only possible if the right index exists, and the planner needs decent statistics to realize the index is worth using. |
| A full table scan | Always available — needs no index, works for any query. For a small table it's perfectly fine and the planner will pick it on purpose. | Reads every row (O(n)); on a huge table that's slow. If you see a scan on a big table in EXPLAIN, you probably want an index. |
Questions you might have
▸If I write the query, why doesn't the database just do exactly what I wrote, in order?
Because you didn't write an order — you wrote a goal. SQL is declarative: 'give me Pune users' has no steps in it. The database is free to reach that goal any way it likes, so it picks the fastest way it can find. That freedom is a feature: the same query you wrote last year can run faster today just because the database got smarter or you added an index.
▸How does the database know an index will be faster WITHOUT trying both?
It estimates. The database keeps rough statistics about each table — roughly how many rows there are, how many distinct values a column has. The planner uses those numbers to guess the cost of each plan ('a scan reads a million rows; the index reads fifty') and picks the cheaper guess. It's an educated estimate, not a full trial run.
▸Can the planner ever pick a BAD plan?
Yes, occasionally — usually when its statistics are stale or the data is unusual, so its cost estimate is wrong. That's exactly when EXPLAIN helps: you look at the plan it chose, spot the surprise (like a scan where you expected an index), and fix it — often by adding an index or refreshing the table's statistics.
▸What's a 'join' doing in the plan?
A join is the step that COMBINES two tables by their matching ids — like pulling each order together with the user who placed it. If your query asks for columns from more than one table, the plan includes a join step to stitch them. There's a whole topic on joins; here it's just one box in the pipeline.
▸Do I have to run EXPLAIN every time I query?
No — normal queries just run; EXPLAIN is a tool you reach for only when something is mysteriously slow and you want to see WHY. It's like opening the maps app's route details: you don't need it to drive, but it's invaluable when you want to understand the path the database chose.
Best read after: What is SQL?