This blog is an EXPLAIN FORMAT=JSON wrap-up for the series of posts I’ve done in the last few months.
In this series, we’ve discussed everything unique to EXPLAIN FORMAT=JSON. I intentionally skipped a description of members such as table_name, access_type or select_id, which are not unique.
In this series, I only mentioned in passing members that replace information from the Extra column in the regular EXPLAIN output, such as using_join_buffer , partitions, using_temporary_table or simply message. You can see these in queries like the following:
1 2 3 4 5 6 7 8 9 | mysql> explain format=json select rand() from dual *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "message": "No tables used" } } 1 row in set, 1 warning (0.00 sec) |
Or
1 2 3 4 5 6 7 8 9 | mysql> explain format=json select emp_no from titles where 'Senior Engineer' = 'Senior Cat' *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "message": "Impossible WHERE" } } 1 row in set, 1 warning (0.01 sec) |
Their use is fairly intuitive, similar to regular EXPLAIN, and I don’t think one can achieve anything from reading a blog post about each of them.
The only thing left to list is a Table of Contents for the series:
attached_condition: How EXPLAIN FORMAT=JSON can spell-check your queries
used_columns: EXPLAIN FORMAT=JSON tells when you should use covered indexes
used_key_parts: EXPLAIN FORMAT=JSON provides insight into which part of multiple-column key is used
EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness
grouping_operation, duplicates_removal: EXPLAIN FORMAT=JSON has all details about GROUP BY
ordering_operation: EXPLAIN FORMAT=JSON knows everything about ORDER BY processing
EXPLAIN FORMAT=JSON knows everything about UNIONs: union_result and query_specifications
EXPLAIN FORMAT=JSON: buffer_result is not hidden!
EXPLAIN FORMAT=JSON: cost_info knows why optimizer prefers one index to another
EXPLAIN FORMAT=JSON: nested_loop makes JOIN hierarchy transparent
Thanks for following the series!