Explain [extended] lays out the optimiser's execution plan for a query. Since version 5.6.3, turning on the optimiser trace facility for a session can show alternative plans that the optimiser considered. The next query is written to information_schema.optimizer_trace.query, the optimizer trace is written to information_schema.optimizer_trace.trace ...
-- ENABLE OPTIMIZER_TRACE set optimizer_trace="enabled=on"; -- RUN THE QUERY select ...; -- RETRIEVE OPOTIMIZER_TRACE OUTPUT select * from information_schema.optimizer_trace;If
information_schema.optimizer_trace.missing_bytes_beyond_max_mem_size is not empty, increase available memory for the trace ...
set optimizer_trace_max_mem_size=1000000;and run the query again. Remember to turn tracing off when you're done.
Last updated 3 Jul 2019
