Oleg Smirnov added a comment - 2022-06-27 05:30 The proposed format of the output is as follows. Suggestions/corrections are welcome. ---------------------------- -- REGULAR TABLES ---------------------------- create table t1 (a int ); insert into t1 values (0),(1),(2),(3);   create table t2 (a int primary key , b int ) as select a, a as b from t1 where a in (1,2);   create table t3 (a int primary key , b int ) as select a, a as b from t1 where a in (1,3);   -- Elimination of a single table explain select t1.a from t1 left join t2 on t2.a=t1.a; + ------+-------------+-------+------+---------------+------+---------+------+------+------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+------+---------------+------+---------+------+------+------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | NULL | NULL | t2 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | + ------+-------------+-------+------+---------------+------+---------+------+------+------------+ 2 rows in set (1.924 sec)   -- Elimination of multiple tables explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a; + ------+-------------+-------+------+---------------+------+---------+------+------+------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+------+---------------+------+---------+------+------+------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | NULL | NULL | t3 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | | NULL | NULL | t2 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | + ------+-------------+-------+------+---------------+------+---------+------+------+------------+ 3 rows in set (1.626 sec)   ---------------------------- -- DERIVED TABLES ---------------------------- create table t11 (a int not null , b int , key (a)); insert into t11 select A.seq, A.seq+B.seq from seq_1_to_10 A, seq_1_to_100 B;   create table t12 (pk int primary key , col1 int ); insert into t12 select seq, seq from seq_1_to_1000;   -- Whole derived table is eliminated explain select t1.* from t1 left join ( select t11.a as a, count (*) as b from t11 left join t12 on t12.pk=t11.b group by t11.a) v2b on v2b.a=t1.a;   + ------+-------------+------------+------+---------------+------+---------+------+------+------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+------------+------+---------------+------+---------+------+------+------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | NULL | NULL | <derived2> | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | | NULL | NULL | t12 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | | NULL | NULL | t11 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | + ------+-------------+------------+------+---------------+------+---------+------+------+------------+   -- Only one table from the derived one is eliminated explain select t1.* from t1 left join ( select t11.a as a, count (*) as b from t11 left join t12 on t12.pk=t11.b group by t11.a) v2b on v2b.b=t1.a; + ------+-------------+------------+-------+---------------+------+---------+----------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+------------+-------+---------------+------+---------+----------------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 8 | mdev28869.t1.a | 10 | Using where | | 2 | DERIVED | t11 | index | NULL | a | 4 | NULL | 1000 | | | NULL | NULL | t12 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | + ------+-------------+------------+-------+---------------+------+---------+----------------+------+-------------+ Same queries explained in JSON format   -- Elimination of a single table explain format=JSON select t1.a from t1 left join t2 on t2.a=t1.a; | { "query_block" : { "select_id" : 1, "const_condition" : "1" , "nested_loop" : [ { "table" : { "table_name" : "t1" , "access_type" : "ALL" , "rows" : 2, "filtered" : 100 } }, { "table" : { "table_name" : "t2" , "eliminated" : true } } ] } } |   -- Elimination of multiple tables explain format=JSON select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a; | { "query_block" : { "select_id" : 1, "const_condition" : "1" , "nested_loop" : [ { "table" : { "table_name" : "t1" , "access_type" : "ALL" , "rows" : 2, "filtered" : 100 } }, { "table" : { "table_name" : "t3" , "eliminated" : true } }, { "table" : { "table_name" : "t2" , "eliminated" : true } } ] } } |   -- Whole derived table is eliminated (note that "eliminated" is printed instead of "materialized" for the derived table) explain format=JSON select t1.* from t1 left join ( select t11.a as a, count (*) as b from t11 left join t12 on t12.pk=t11.b group by t11.a) v2b on v2b.a=t1.a; | { "query_block" : { "select_id" : 1, "const_condition" : "1" , "nested_loop" : [ { "table" : { "table_name" : "t1" , "access_type" : "ALL" , "rows" : 2, "filtered" : 100 } }, { "table" : { "table_name" : "<derived2>" , "eliminated" : { "nested_loop" : [ { "table" : { "table_name" : "t12" , "eliminated" : true } }, { "table" : { "table_name" : "t11" , "eliminated" : true } } ] } } } ] } } |   -- Only one table from the derived one is eliminated explain format=JSON select t1.* from t1 left join ( select t11.a as a, count (*) as b from t11 left join t12 on t12.pk=t11.b group by t11.a) v2b on v2b.b=t1.a; | { "query_block" : { "select_id" : 1, "const_condition" : "1" , "nested_loop" : [ { "table" : { "table_name" : "t1" , "access_type" : "ALL" , "rows" : 2, "filtered" : 100 } }, { "table" : { "table_name" : "<derived2>" , "access_type" : "ref" , "possible_keys" : [ "key0" ], "key" : "key0" , "key_length" : "8" , "used_key_parts" : [ "b" ], "ref" : [ "mdev28869.t1.a" ], "rows" : 10, "filtered" : 100, "attached_condition" : "trigcond(v2b.b = t1.a and trigcond(t1.a is not null))" , "materialized" : { "query_block" : { "select_id" : 2, "const_condition" : "1" , "nested_loop" : [ { "table" : { "table_name" : "t11" , "access_type" : "index" , "key" : "a" , "key_length" : "4" , "used_key_parts" : [ "a" ], "rows" : 1000, "filtered" : 100 } }, { "table" : { "table_name" : "t12" , "eliminated" : true } } ] } } } } ] } } | If the proposed format is OK I'll move on with updating test cases and cleaning up the code.

Sergei Petrunia added a comment - 2022-06-28 13:08 A more interesting question is what to show when the derived table is eliminated ( MDEV-26278 makes it possible): Consider this query: explain select t1.* from t1 left join (select t11.a as a, count(*) as b from t11 left join t12 on t12.pk=t11.b group by t11.a ) as TBL on TBL.a=t1.a; After MDEV-26278 in 10.10, it prints this: +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | | +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ The question is should it print this: +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | <derived2> | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ Or also print the subquery contents: +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | <derived2> | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | | 2 | DERIVED | t11 | index | NULL | a | 4 | NULL | 1000 | | +------+-------------+------------+-------+---------------+------+---------+-----------+------+-------------+

Sergei Petrunia added a comment - 2022-06-28 13:45 ... and re-reading the first comment by oleg.smirnov , I see that it's neither of the above. It is (copy-pasting): -- Whole derived table is eliminated explain select t1.* from t1 left join (select t11.a as a, count(*) as b from t11 left join t12 on t12.pk=t11.b group by t11.a) v2b on v2b.a=t1.a; +------+-------------+------------+------+---------------+------+---------+------+------+------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | NULL | NULL | <derived2> | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | | NULL | NULL | t12 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | | NULL | NULL | t11 | NULL | NULL | NULL | NULL | NULL | NULL | Eliminated | +------+-------------+------------+------+---------------+------+---------+------+------+------------+ That is, the subquery is printed but we can only see "Eliminated" for all tables in it.

Oleg Smirnov added a comment - 2022-06-30 13:12 After discussion we decided that the output must look like this. MariaDB [mdev28869]> explain select t1.a from t1 left join t2 on t2.a=t1.a; + ------+-------------+-------+------------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+-------+------------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | 1 | SIMPLE | t2 | Eliminated | NULL | NULL | NULL | NULL | NULL | NULL | + ------+-------------+-------+------------+---------------+------+---------+------+------+-------+ 2 rows in set (0.028 sec)   MariaDB [mdev28869]> explain select t1.* from t1 left join -> ( select t11.a as a, count (*) as b -> from t11 left join t12 on t12.pk=t11.b -> group by t11.a) v2b on v2b.a=t1.a; + ------+-------------+------------+------------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ------+-------------+------------+------------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | | | 1 | PRIMARY | <derived2> | Eliminated | NULL | NULL | NULL | NULL | NULL | | | 2 | DERIVED | t12 | Eliminated | NULL | NULL | NULL | NULL | NULL | | | 2 | DERIVED | t11 | Eliminated | NULL | NULL | NULL | NULL | NULL | | + ------+-------------+------------+------------+---------------+------+---------+------+------+-------+   MariaDB [mdev28869]> explain format=JSON select t1.a from t1 left join t2 on t2.a=t1.a; | { "query_block" : { "select_id" : 1, "const_condition" : "1" , "nested_loop" : [ { "table" : { "table_name" : "t1" , "access_type" : "ALL" , "rows" : 2, "filtered" : 100 } }, { "table" : { "table_name" : "t2" , "access_type" : "Eliminated" } } ] } } |   MariaDB [mdev28869]> explain format=JSON select t1.* from t1 left join ( select t11.a as a, count (*) as b from t11 left join t12 on t12.pk=t11.b group by t11.a) v2b on v2b.a=t1.a; | { "query_block" : { "select_id" : 1, "const_condition" : "1" , "nested_loop" : [ { "table" : { "table_name" : "t1" , "access_type" : "ALL" , "rows" : 2, "filtered" : 100 } }, { "table" : { "table_name" : "<derived2>" , "access_type" : "Eliminated" , "materialized" : { "query_block" : { "select_id" : 2, "eliminated" : true , "nested_loop" : [ { "table" : { "table_name" : "t12" , "access_type" : "Eliminated" } }, { "table" : { "table_name" : "t11" , "access_type" : "Eliminated" } } ] } } } } ] } } |