This SQL System Changes How You Write Code

Added: 2026-05-06

[00:00:00]You know what an execution plan is. You know what a correlated subquery costs.

[00:00:05]You know why wrapping a column in a function stops the database from using an index. But knowing individual concepts isn't the same thing as having a system. And without a system, the next time you open a slow query, you'll still have that feeling of where do I even start? That's what this video is for.

[00:00:20]We're going to combine several concepts into one clear framework, so you can see how it all connects. And so you know exactly what to do the next time a query is slow. Most SQL developers approach SQL performance the same way. They have a query that's slow, so they start changing things. They add an index, rewrite a join, or move things around.

[00:00:39]And then they run it again and see if it's faster. Sometimes it works. More often it doesn't. And either way, they don't really know why. The reason this happens is that there's no model connecting the different pieces.

[00:00:52]Execution plans feel abstract and hard to understand. Index decisions feel like guesswork. What we've been building across this series of videos is a way of thinking that connects all of those things. A repeatable process you can apply to any slow query on any database.

[00:01:08]By the end of this video, you'll have that process clearly laid out in one place. And if you want a simple reference you can keep handy while you're working, my SQL performance checklist covers the key things to look for in any slow query. The link is in the description. Let's start at the beginning of the series because this is the foundation that everything else rests on. When a query is slow, there are really only three places to look.

[00:01:32]Poor query design, missing indexes, or poor database design. That sounds simple, but the reason it matters is what it rules out. Most developers, when they hit a slow query, start by assuming the database is just struggling. That the server needs more memory or the database engine is inefficient. Or there's something fundamentally wrong with the system. And that assumption almost always leads them in the wrong direction. The database engine is actually very good at what it does.

[00:01:59]It has an optimizer that looks at your query, looks at the data, and tries to find the best way to get the result.

[00:02:06]This is true in most modern database engines.

[00:02:09]The details are different, but they all have an optimizer.

[00:02:13]The problem is usually what you're giving the engine to work with. SQL is a declarative language. You describe the result you want. You don't tell the database how to get it.

[00:02:24]That can be helpful because it means a lot of the complexity is handled for you.

[00:02:29]But it's also where confusion comes from because when something is slow, there's no obvious line of code to point at.

[00:02:36]So, the first change in thinking is this. Instead of asking, "Why is the database slow?" ask, "Which of these three things is the problem? Poor query design, missing indexes, or poor database design?" Once you've narrowed it down, you know where to focus. The execution plan is what helps you answer that question, which is exactly what we cover next. The execution plan is the single most useful tool you have for diagnosing a slow query. And it's the thing most developers skip entirely. The reason people avoid it is that it looks intimidating. There are icons, numbers, strange terms, arrows going in different directions, and it looks different in every SQL editor and every vendor, which makes it feel like something you have to learn separately for each tool. I can relate to this. It's a pretty confusing concept, and it seems like it could be really improved in many editors. But the underlying idea is the same everywhere.

[00:03:29]The execution plan shows you the steps the database takes to run your query.

[00:03:33]Think of it as the database showing its work. And you don't need to understand every detail of every step. You need to be able to answer three questions. What tables is the database reading and in what order? How much data is it processing at each step? And where is most of the work happening? If you can answer those three questions, you can start diagnosing the problem. One of the most useful things to look at is how each table is being accessed. A sequential scan, or a full table scan, means the database is reading every row in the table. That's not always a problem. On a small table, it's fine. If the query is returning most of the rows anyway, it can actually be more efficient than using an index. But if you see a full table scan on a large table, and you're only expecting a small number of rows back, that's usually a sign that something is worth looking into. An index seek, on the other hand, means the database is jumping into exactly the rows it needs. That's generally the most efficient access method. The cost values in the plan are not units of time. They're a relative measure that the database uses to compare different approaches. What you're looking for is which steps are dominating the overall cost. That's where the work is concentrated, and that's where you'd start. The practical version of all this is, run the execution plan before you change anything. If you don't measure before you start, you have no way of knowing whether your change has actually helped.

[00:04:55]Once you know how to read a plan, the next obstacle is the advice you've probably already heard. Pieces of guidance that sound reasonable, but lead you in the wrong direction. We covered five of them in this series. The first is that joins are slow. This one gets repeated a lot, and it leads developers to denormalize their tables, combine data into fewer tables, and avoid joins wherever possible. But joins are not a nice-to-have feature. They're fundamental to how relational databases work. The query engine is specifically built to run them efficiently. The join itself is almost never the problem.

[00:05:28]Missing indexes on the columns being joined, or joining large unfiltered result sets, those are the problems, not the join. The second is that more indexes always make things faster.

[00:05:39]Indexes help with reads. When you're filtering or joining on a column, an index on that column can allow the database to find the right rows quickly without scanning the whole table. But every index has a cost on the right side. Inserts, updates, and deletes all have to update every index on that table. And the database doesn't always use the index you create. If a column doesn't have many distinct values, the database might decide that the table scan is actually faster. More indexes are not always better.

[00:06:09]Targeted indexes, based on what the execution plan is telling you, is better. The third myth is that subqueries are always slow. This comes from older versions of database engines that didn't handle certain subquery patterns well. Modern databases are much better at this, and the optimizer will often convert a subquery into a join internally anyway. The actual problem is correlated subqueries, which are subqueries that reference a column from the outer query, and therefore have to run once for every row in the outer result. That's where the cost is. A simple subquery in a where clause is usually fine. A correlated subquery in the select clause, running hundreds of thousands of times, is not. The fourth is that select star is harmless. For exploring data, it's a reasonable shortcut. But in queries that run regularly, selecting every column when you only need a few, means the database is retrieving and transferring data you're not going to use. The fifth is that if a query is slow, the database just needs more hardware. I've said this already in this video, but it's worth repeating here because it's the most expensive misconception of the five.

[00:07:15]Most performance problems can be solved with query design, indexes, or both.

[00:07:20]Hardware can be expensive to implement.

[00:07:23]Query changes are much cheaper. The common thread across all five of these is the same. The execution plan is the first step and the thing to guide you.

[00:07:31]Not the advice you've heard, not the pattern someone shared on a blog. The plan that the database has is what to focus on. The different videos that I share about improving the performance of a query followed the same process, even though the queries were different and the databases were different. That process is worth making explicit because it's what you actually take away from this series. The first step is understanding what the query is supposed to do. Not how it's written. What result it is meant to produce and why. This matters because it's the only way to know whether a change you make is producing the right data, not just faster data. The second step is establishing a baseline. Run the query, measure the time, save the execution plan. You need this before you touch anything because without it, you have no way to evaluate whether your change has helped or by how much. The third step is reading the execution plan and finding the highest cost steps. Not every step, not every detail. Just focus on the steps where most of the work is concentrated. The fourth step is forming a hypothesis. This means a theory on why you think a query is slow and a possible way to improve it.

[00:08:37]One cause at a time and one change at a time.

[00:08:40]If you make three changes at once and the query gets faster, you don't know which change helped. You don't know which of the other two might have made things worse and been masked by the one that helped. One change at a time. The fifth step is measuring again and comparing against the baseline.

[00:08:58]Then you repeat the cycle. The developers who constantly fix slow queries aren't faster at guessing.

[00:09:04]They're actually slower and more deliberate. They read the plan first.

[00:09:07]They form one hypothesis, and then they measure and repeat. The developers who stay stuck are the ones who make three changes at once and hope one of them works. They treat performance as something unpredictable, as though the database is making arbitrary decisions that can't be understood. The database is not arbitrary. It follows rules. It uses statistics. It picks a plan based on the information it has. When the plan is wrong, it's usually because the statistics are outdated, or the query is written in a way that prevents the optimizer from making a good choice, or the underlying data structure doesn't suit the access pattern. All of those things can be investigated. And once you can diagnose them, you can fix them.

[00:09:49]That's the process that this video series has been building. A way of thinking about what the database is doing, why it's doing it, and how to change the outcome. The root causes of slow queries are query design, indexes, and database design. Hardware is almost never the real issue. The execution plan shows you which of those three is the real issue. Learn to read that before changing anything. The common myths, such as joins are slow, more indexes are better, subqueries should always be rewritten, are all versions of the same mistake, acting on assumptions instead of reading the plan. The investigation process is always the same. Understand the query, establish a baseline, form one hypothesis, make one change, and measure. The shifting thinking in this series is trying to create something straightforward.

[00:10:35]You should focus on diagnosing the problem with a query using the execution plan. This will change how you work with SQL, not just the queries you've written in the past, but the ones you write from now into the future. If you followed this series and you're thinking, "I want to apply this process to my own queries on my own databases with someone walking me through it," that's what Write Faster SQL is for.

[00:10:57]It covers execution plans and performance issues across major database vendors, how to identify why your query is slow, and the full optimization process we've been using throughout this series, taught step-by-step with real examples. It's not just theory, it's the same process that you've seen across this series of videos, just structured so you can use it yourself. The link to Write Faster SQL is in the description if you want to take a look. Thanks for watching.