Secret To Optimizing SQL Queries - Understand The SQL Execution Order

Where is the translation of the CC?

Additionally, for the optimizer to "make up" a reasonably good plan (from the various alternatives), it needs to know a bit about the data (value) distribution. This is where STATISTICS / ANALYZE (depends on the DB vendor) come handy. It helps the optimizer do estimates for the various steps (rows, size of data, etc.) of each plan, and figure out which of the different plans is the best candidate to execute. Therefore it is important to collect this information on critical columns (usually join, where clause columns). It is also important to keep this information regularly refreshed so that the optimizer does not make bad decisions based on stale statistics. Very bad things can happen with stale statistics.

You should film on the shadow side of your face and make the light not so high, can be helpful to set the mood lol

Can you make a video explaining the difference between system design and software architecture?

Superb video! Simple explanation on query optimisation.

Can anyone help me when does the function count or sum will be executed will it be after limit ?

Amazing. Thank you!

Well explained. However I do miss 1) the generation of more query-plans and selection amongs them (cost estimations) and (as an element herein) 2) different table access tactics (sequential scan, index access or index only).

thanks, helped clear up some issues I had.

Something doesn't add well here. If you notice HAVING clause refers to 'total_spent' which is defined in SELECT, so dependency wise HAVING should be after SELECT and not before it.

please speak in hindi

The way you explained with the animations are Awesome. Great Job. Very Well Explained.

I always thought that the SELECT happened before HAVING, considering that we can use SELECT aliases in the HAVING filter.

For those are confused on the execution steps (how could total_spent be recognized if select statement order is after having statement). I did some researches and Alex is right!

In relational databases, the operations in a query don't necessarily execute in the order they're written. The database optimizer determines the most efficient way to execute the query. However, conceptually, the SELECT clause's role can be understood as follows:

1. Table Scans & Join: The database engine identifies the necessary rows and columns from the tables involved. Even though the SELECT clause is written at the beginning, the actual columns or computed values aren't fetched or computed just yet. The engine is aware of them but doesn't process them immediately.
2. WHERE Filtering: Rows are filtered based on the WHERE condition.
3. Grouping & Aggregation: The engine groups the rows and computes the aggregate functions. At this stage, the values specified in the SELECT clause start to take shape. For example, the count of order_id and sum of order_amount are computed for each group.
4. HAVING Filtering: The groups are further filtered.
5. SELECT: The specific columns and computed values defined in the SELECT clause are finalized. The customer_id, total_orders, and total_spent values for each group are determined.
6. Ordering: The results are ordered.
7. Limiting: The result set is limited.

So, while the database is aware of the SELECT clause from the beginning, the actual selection and computation of the specified columns and values are intertwined with the grouping and aggregation process. The exact timing and order can vary based on the database system and its optimizer's decisions.

I could not place the link here but you can easily found it on Microsoft docs and stackoverflow.

cool, didn't think it's possible to include all these concepts in 6 min video. One thing, it's great to watch it when you want to summarise already existing knowledge

Why are we using HAVING total_spent >_ 1000, but not WHERE total_spent >_ 1000 ? Can you please explain?

Nice and simple explanation.Thanks

Thanks for your sharing Bro's.

bro this way of teaching is really really make sense. thanks a lot for these visuals.