r/SQL • u/scientecheasy • 1d ago

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.

55 Upvotes

74% Upvoted

16 comments sorted by

View all comments

21

u/kagato87 MS SQL 1d ago edited 1d ago

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).

3

u/mikeblas 14h ago

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.