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