Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
    
+1 for using new information_schema table for MySQL 5.6. –  RolandoMySQLDBA May 20 '13 at 17:38

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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