MySQL Bugs: #33486: Optimizer trace tool needed

Bug #33486	Optimizer trace tool needed
Submitted:	22 Dec 2007 21:04	Modified:	8 Dec 2011 3:35
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	debug, Optimizer, trace

Description:
Sergey Petrunia has written about this -- http://s.petrunia.net/blog/?p=23

A mechanism to trace the work of the optimizer is needed. When debugging a bad plan, there is not much that can be done besides looking at explain plan, SHOW INDEXES and actual selectivities. All the choices made by the optimizer are secret, except for the final choices. This will become more serious with each release as more features are added to the optimizer because more features means more decisions and more decisions means more incorrect decisions in the optimizer.

How to repeat:
N/A

Suggested fix:
N/A

Thank you for a reasonable feature request.

This has been implemented in 5.6.3. Not all decisions are traced yet, but quite a few are.
Doc:
http://forge.mysql.com/wiki/MySQL_Internals_Optimizer_tracing
and also
http://jorgenloland.blogspot.com/2011/10/optimizer-tracing-query-execution-plan.html
http://guilhembichot.blogspot.com/2011/09/optimizer-tracing-how-to-configure-it.html

Wow, nice work