EXPLAIN FORMAT=JSON in MySQL
You are viewing an old version of this article. View
the current version here.
Contents
This is a page for listing thing that we are not happy with in MySQL/Oracle's implementation of EXPLAIN FORMAT=JSON.
High priority
These will definitely be fixed
Join buffering doesn't show condition for buffered table
MySQL [dbt3sf1]> explain format=json select * from orders,customer where o_orderdate between '1995-01-01' and '1995-02-02' and c_acctbal <0 and o_custkey + 1 = c_custkey -1 \G
EXPLAIN: { "query_block": { "select_id": 1, "nested_loop": [ { "table": { "table_name": "orders", "access_type": "range", "possible_keys": [ "i_o_orderdate" ], "key": "i_o_orderdate", "used_key_parts": [ "o_orderDATE" ], "key_length": "4", "rows": 40676, "filtered": 100, "index_condition": "(`dbt3sf1`.`orders`.`o_orderDATE` between '1995-01-01' and '1995-02-02')" } }, { "table": { "table_name": "customer", "access_type": "ALL", "possible_keys": [ "c_acctbal", "i_c_acctbal_nationkey" ], "rows": 149415, "filtered": 18.037, "using_join_buffer": "Block Nested Loop", "attached_condition": "((`dbt3sf1`.`customer`.`c_acctbal` < 0) and ((`dbt3sf1`.`orders`.`o_custkey` + 1) = (`dbt3sf1`.`customer`.`c_custkey` - 1)))" } } ] } }
Nice to have
These are not show-stoppers, but are highly-desirable.
Range doesn't show the ranges that are scanned
Optimizer trace has this information, but it would be nice if EXPLAIN showed the ranges for the query plan.
MySQL [dbt3sf1]> explain format=json select * from customer where c_acctbal < -1000 \G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "table": { "table_name": "customer", "access_type": "range", "possible_keys": [ "c_acctbal", "i_c_acctbal_nationkey" ], "key": "c_acctbal", "used_key_parts": [ "c_acctbal" ], "key_length": "9", "rows": 1, "filtered": 100, "index_condition": "(`dbt3sf1`.`customer`.`c_acctbal` < -(1000))" } } }
Smaller stuff
index_merge is show in a non-JSON way.
index_merge is shown in the old, "tabular" way. JSON allows to use structures, why does it still have things like "key":"union(key1,key2)" ?
MySQL [dbt3sf1]> explain format=json select * from orders where o_orderDATE='1995-01-01' OR o_custkey=1243\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "table": { "table_name": "orders", "access_type": "index_merge", "possible_keys": [ "i_o_orderdate", "i_o_custkey" ], "key": "union(i_o_orderdate,i_o_custkey)", "key_length": "4,5", "rows": 634, "filtered": 100, "attached_condition": "((`dbt3sf1`.`orders`.`o_orderDATE` = '1995-01-01') or (`dbt3sf1`.`orders`.`o_custkey` = 1243))" } } }
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.