7

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?

9

The recent 5.6 version has added this feature.

See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer

Typical Usage:

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

  • +1 for using new information_schema table for MySQL 5.6. – RolandoMySQLDBA May 20 '13 at 17:38
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.