Details

    • Type: Technical task
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: N/A
    • Fix Version/s: 10.5
    • Component/s: Optimizer
    • Labels:
      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)

        Attachments

          Activity

            People

            • Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: