How does the optimizer compare different execution plans of the same query? Let's say we have two complex and structurally different execution plans, how can I know if they are providing the same result?
closed as too broad by Kin Shah, Martin Smith, Mikael Eriksson, RolandoMySQLDBA, ypercubeᵀᴹ Mar 11 '14 at 18:58
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
The task of an optimizer is to create several execution plans and compare them (typically by costs). Each execution plan can be seen as logical transformation.
For example: a simple join would always be the cartesian product of two tables. Each row from the first is read and combined with each row in the other table. Than the join condition is checked and the resulting row is used or discarded.
That would be our live without optimizer. The optimizer is using set theory and boolean algebra to transform it. Each transformation leads to the same result. For instance tt doesn't matter if you first sort two sets independent and join them afterwards or first join them and sort them as one.
Other optimizations are the way rows are fetched or compared. There are for example many join algorithms like nested loop join, hash join or sort-merge join. Each algorithm has different advantages and disadvantages. But they operate on the same data. The only (hypothetical) way a execution plan could lead to other results is a difference in rows stored and the index. For that reason the indices have to be always updated as a row is inserted/updated/deleted.