If you want to know the secret to optimizing SQL queries, open this: 🧵
Before you can optimize SQL queries, there's one thing you should know:
𝐓𝐡𝐞𝐢𝐫 𝐞𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧 𝐨𝐫𝐝𝐞𝐫.
A SQL query executes its statements in the following order:
1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET
𝐓𝐡𝐞𝐢𝐫 𝐞𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧 𝐨𝐫𝐝𝐞𝐫.
A SQL query executes its statements in the following order:
1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET
The techniques you implement at each step help speed up the following steps.
This is why it's important to know their execution order.
To maximize efficiency, focus on optimizing the steps earlier in the query.
With that in mind, let's take a look at some optimization tips:
This is why it's important to know their execution order.
To maximize efficiency, focus on optimizing the steps earlier in the query.
With that in mind, let's take a look at some optimization tips:
1. Maximize the WHERE clause
This clause is executed early, so it's a good opportunity to reduce the size of your data set before the rest of the query is processed.
2. Filter your rows before a JOIN
Although the FROM/JOIN occurs first, you can still limit the rows.
This clause is executed early, so it's a good opportunity to reduce the size of your data set before the rest of the query is processed.
2. Filter your rows before a JOIN
Although the FROM/JOIN occurs first, you can still limit the rows.
To limit the number of rows you are joining, use a subquery in the FROM statement instead of a table.
3. Use WHERE over HAVING
The HAVING clause is executed after WHERE & GROUP BY.
This means you're better off moving any appropriate conditions to the WHERE clause when you can.
3. Use WHERE over HAVING
The HAVING clause is executed after WHERE & GROUP BY.
This means you're better off moving any appropriate conditions to the WHERE clause when you can.
4. Don't confuse LIMIT, OFFSET, and DISTINCT for optimization techniques.
Is easy to assume that these would boost performance b minimizing the data set, but this isn’t the case.
Because they occur at the end of the query, they make little to no impact on its performance.
Is easy to assume that these would boost performance b minimizing the data set, but this isn’t the case.
Because they occur at the end of the query, they make little to no impact on its performance.
If you want to create efficient queries, it's a good idea to understand how things work under the hood otherwise your efforts may be wasted.
While these tips work best in most cases, you should consider your unique use case when choosing the best course of action.
While these tips work best in most cases, you should consider your unique use case when choosing the best course of action.
That's a wrap!
Want more engineering insights like this?
Subscribe to our 𝐟𝐫𝐞𝐞 𝐧𝐞𝐰𝐬𝐥𝐞𝐭𝐭𝐞𝐫 for a weekly deep-dive and roundup of all our best content:
blog.levelupcoding.co
Want more engineering insights like this?
Subscribe to our 𝐟𝐫𝐞𝐞 𝐧𝐞𝐰𝐬𝐥𝐞𝐭𝐭𝐞𝐫 for a weekly deep-dive and roundup of all our best content:
blog.levelupcoding.co
جاري تحميل الاقتراحات...