Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information like, which plans the optimizer considers, the cost of each plan?
The recent 5.6 version has added this feature.
# Turn tracing on (it's off by default): SET optimizer_trace="enabled=on"; SELECT ...; # your query here SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; # possibly more queries... # When done with tracing, disable it: SET optimizer_trace="enabled=off";
You can see it in action in SQL-Fiddle. Not sure how the output can be beautified.
The best I can immediately mention is to simply run EXPLAIN on a query.
There is a graphical way to do this
As for steps to optimizing a query, a parse tree is created. Rather than explaining the parser here, please read my post from Mar 11, 2013 (Is there an execution difference between a JOIN condition and a WHERE condition?) on what the parser does.