5 steps to write SQL queries that actually perform well:
1. Start with your WHERE clause
Why?
Because filtering early reduces the dataset size before expensive
operations like JOINs and GROUP BYs kick in.
Plan your filters first, then build around them.
Why?
Because filtering early reduces the dataset size before expensive
operations like JOINs and GROUP BYs kick in.
Plan your filters first, then build around them.
2. Use Indexes
They help database find specific information much faster without having to search everything.
Pro tip: Composite indexes give you a huge benefit.
If you're regularly filtering on (date, status, region), create one index on all three columns in that order.
They help database find specific information much faster without having to search everything.
Pro tip: Composite indexes give you a huge benefit.
If you're regularly filtering on (date, status, region), create one index on all three columns in that order.
3. Don't use SELECT *
SELECT * pulls every column, even ones you don't need.
More data = slower queries.
More data = higher memory usage.
Only select what you actually use.
SELECT * pulls every column, even ones you don't need.
More data = slower queries.
More data = higher memory usage.
Only select what you actually use.
4. JOINs have an order, and it matters
Start with your smallest table.
Join to progressively larger tables.
Think of it like building a foundation:
- Small table = solid foundation.
- Large table joined to small result = manageable.
- Large table joined to large table = performance nightmare.
Also: INNER JOINs before LEFT JOINs when possible to filter results early.
Start with your smallest table.
Join to progressively larger tables.
Think of it like building a foundation:
- Small table = solid foundation.
- Large table joined to small result = manageable.
- Large table joined to large table = performance nightmare.
Also: INNER JOINs before LEFT JOINs when possible to filter results early.
5. Test with increasing table sizes
Your query runs fast on 1,000 rows?
Great.
Will it run fast on 10 million rows?
Probably not.
Always test with production-sized datasets and split up large queries into supporting tables if needed.
Your query runs fast on 1,000 rows?
Great.
Will it run fast on 10 million rows?
Probably not.
Always test with production-sized datasets and split up large queries into supporting tables if needed.
TL;DR:
- Write WHERE clauses first to filter early
- Index every column you filter, join, or sort on
- Select only columns you need (never SELECT *)
- Join smallest tables first, then build up
- Test progressively
- Write WHERE clauses first to filter early
- Index every column you filter, join, or sort on
- Select only columns you need (never SELECT *)
- Join smallest tables first, then build up
- Test progressively
I hope you've found this thread helpful.
If you did, I ask for 2 small favours:
1. Follow me @imtommitchell for more like this daily.
2. Click below, jump to the top and share to help someone else.
If you did, I ask for 2 small favours:
1. Follow me @imtommitchell for more like this daily.
2. Click below, jump to the top and share to help someone else.
https://twitter.com/1635787028603236352/status/1960266727478202734
P.S. If you're interested in data you'll love my weekly newsletter.
I share everything I know about building high-paying data skills from my 8+ years in the industry.
Subscribe for free here:
thedatadose.com
I share everything I know about building high-paying data skills from my 8+ years in the industry.
Subscribe for free here:
thedatadose.com
• • •
Missing some Tweet in this thread? You can try to
force a refresh
