My favorite SQL optimization tips ⚡️

First things I do to make my queries run FASTER and more EFFICIENTLY

Ever had a SQL query take over a few minutes to run? So you go grab a coffee, or you pick up your phone and start scrolling on Instagram… And before you know it, you’re completely distracted from the analysis at hand?

That to me is the worst part of an inefficient query — when it disrupts your flow, and your productivity takes a hit.

But also slow queries load the entire database system, and makes other people’s queries run slower too. Then it gets really awkward, when it gets traced back to you, and you’re forever remember as “the one who slowed down the SQL database that one time”.

So today I’m going to share the first things I do to optimize a query. In my experience, just looking at these 3 major buckets of “things” can usually help speed up your query by 20 - 80%..

Optimization 1: Remove any redundant processing

The first thing I always check: am I making the database do extra, unncessary work?

If you’re using SELECT *, you probably are. Because that forces the database to pull every single column, which is super inefficient. So instead, SELECT just the columns you need.

Same idea with DISTINCT, GROUP BY, and correlated subqueries — if you don’t absolutely need them, drop them. These are expensive operations that make your query scan, sort, and reprocess data multiple times.

Similarly… if you don’t need all the rows, like if you’re just testing and building your query, use a LIMIT to reduce the number of rows in returned. I’m always surprised by how much this one makes a difference.

Optimization 2: Reduce size of datasets early

This one’s probably my favorite tip.

If you’re working with multiple large tables, filter early — ideally before you join.

Here’s why: when you join two big tables without filtering first, you’re basically telling SQL to multiply everything by everything. Table A has 10K rows and Table B has 10K rows? Congrats, you’ve just created 100 million combinations.

That’s why I always filter as soon as possible — usually CTEs, less often in subqueries. The smaller the dataset at each step, the faster everything runs.

Optimization 3: Use your indexes

Indexes are like the alphabet tabs in a dictionary — they help your database jump straight to what it needs, instead of flipping through every single page.

When you filter on an indexed column, SQL doesn’t have to scan the entire table. It already knows exactly which chunks of data to look at.

However, there’s a catch (something I wish I had learned sooner). The moment you wrap that column inside a function — say LOWER() or CAST() —the index no longer works. Now the database has to check every single row, as if there is no index.

So here’s what I always do:

• Filter directly on indexed columns

• Avoid functions on indexes inside the WHERE clause

• And if you notice that a column you filter on all the time isn’t indexed? Ask your Data Engineer to add one (or do it yourself!)

Bonus: EXPLAIN Everything

Before you start guessing what’s slowing down your query, make SQL tell you.

Run EXPLAIN or EXPLAIN ANALYZE. It shows you the steps that happen behind the scenes when your query run. As part of the execution plan, it’ll also show which parts of your query are slow, whether indexes are being used, and where the database is wasting time / resources.

The next time you find yourself picking up your phone to doom scroll while your query runs, try these tips instead! I’ve also compiled all of these tips into this cheatsheet. Save this and reference it the next time you have to optimize a query.