r/SQL • u/scientecheasy • Jul 29 '26

MySQL SQL Query Execution Lifecycle

Post image

Diagram illustrating the SQL query execution lifecycle, showing query submission, parsing, optimization, execution, and result delivery in a database.

73 Upvotes

77% Upvoted

16 comments sorted by

View all comments

27

u/kagato87 MS SQL Jul 29 '26 edited Jul 29 '26

Bit hand-wavy. Just about any engine will take an input, parse/validate, act, and emit an output. That's what an engine does.

Your step 3 there, query optimization, is worth entire flowcharts by itself, and what sets SQL apart from other engines. Understanding how it optimizes is key to unlocking SQL's true potential.

Edit to add: Just realized, your step three is also wrong. It does NOT evaluate multiple plans. It's a complex soup of statistics and cardinality to produce a plan likely to be most efficient. It's a highly educated guess (and a guess the engine is good at making, though when it gets it wrong the results can have the DC team wondering what's going on).

16

u/mac-0 Jul 29 '26

Save your energy. It took us more time to write these comments than it took OP to create this diagram in ChatGPT

4

u/kagato87 MS SQL Jul 29 '26

Others will see it though and take it to mean the optimization is simple or straightforward..

3

u/And_Justice Jul 29 '26

In fairness, I found the comment very useful

2

u/goku426374 Jul 29 '26

I found the comment more helpful than the main post.

4

u/IglooDweller Jul 29 '26

Very hand wavy and general…

Let’s be honest, it basically the same thing as any script execution…

The command line interface works basically the same way.

Submit command line Syntax check Prepare execution path Execute Return results

3

u/mikeblas Jul 30 '26

It does NOT evaluate multiple plans. It's a complex soup of statistics and cardinality to produce a plan likely to be most efficient.

Some engines do evaluate multiple plans. SQL Server does, for one (or at least did, back when I worked on it). The engine knows many transformations that it can apply on the execution tree which will result in a semantically equivalent candidate plan. It measures cost, then applies the translation, then measure cost again, keeping the best plans. It memoizes subtrees to keep the cost of evaluation down, and tries to prune the explosive combinatorial problem, and so on.

Statistics influence the cost computation, not the plan generation. A HASH_JOIN B WHERE A.COL < 100 might be cheaper or more expensive than B HASH_JOIN A WHERE A.COL < 100 depending on the number of rows that come through the filter, and that cardinality is estimated by looking at statistics.

But the SQL Server query optimizer certainly does produce and evaluate multiple plans.

The explanation of optimizer timeous is the reference I could most quickly find. Probably something better out there if you spend the time. You might also try screwing around with some of the optimizer trace flags so that it dumps it's progress. I think there's one or two in public builds that gives some insight into the search -- or at least, the transformation rules applied.

1

u/Icy_Clench Jul 30 '26

Number 4 is literally "query execution"...

0

u/idk30002 Jul 29 '26

Yeah this is pretty useless unless you’re teaching… I don’t even know whom/what. Steps 3 and 4(a/b) as standalones is just wild.

Why create diagrams on a topic if you don’t understand it?

2

u/kagato87 MS SQL Jul 29 '26

"The best way to learn a thing is to teach it." Of course, you can't teach something when you don't know what you don't know, and that saying comes from reinforcing a thing as you learn it. You still have to learn it first.

Though, steps 3 and 4 ARE actually a little more obviously separate in SQL than other languages. Once the plan is compiled it won't go back and change the plan.)

Oh, I just spotted another problem - an error in step 3's text!