Details
-
Technical task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
N/A
-
None
Description
This task is concerned with how we would show the sort_nest (MDEV-8306)
in the explain or analyze output.
First thought would be to show the sort nest the same way as we do it for SJ Materialization or Derived tables
This is how it is done for Semi Join Materialization
MariaDB [test]> explain extended select * from t1 where a in (select a from t2 group by a);
|
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 5 | 100.00 | |
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where; Using join buffer (flat, BNL join) |
|
| 2 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | |
|
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
Another one could be not to show the table for sort-nest and just mention in the EXTRA column of explain , using sort_nest with the tables inside it. Instead of using temporary, we can have Using Sort Nest.
MariaDB [test]> explain select * from t1,t2 where t1.a > 95 and t1.a=t2.a order by t2.a+1;
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | ref | a | a | 5 | test.t1.a | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
|
2 rows in set (0.004 sec)