Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
  3. MDEV-20146

Analyze and Explain for sort-nest

Details

    • Technical task
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • N/A
    • 12.1
    • Optimizer
    • 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

            psergei Sergei Petrunia
            varun Varun Gupta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.