Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17568

LATERAL DERIVED is not clearly visible in EXPLAIN FORMAT=JSON

    XMLWordPrintable

    Details

      Description

      Let's take a small example (from main/derived_split_innodb.test) and look how LATERAL DERIVED is shown in EXPLAIN:

      CREATE TABLE t1 (
      n1 int(10) NOT NULL,
      n2 int(10) NOT NULL,
      c1 char(1) NOT NULL,
      KEY c1 (c1),
      KEY n1_c1_n2 (n1,c1,n2)
      ) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a');
      ANALYZE TABLE t1;
      

      EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t                                                  
      WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;                                                                          
      +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
      | id   | select_type     | table      | type  | possible_keys | key      | key_len | ref               | rows | Extra                    |
      +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
      |    1 | PRIMARY         | t1         | index | c1,n1_c1_n2   | n1_c1_n2 | 9       | NULL              |    2 | Using where; Using index |
      |    1 | PRIMARY         | <derived2> | ref   | key0          | key0     | 8       | j5.t1.n1,j5.t1.n2 |    2 |                          |
      |    2 | LATERAL DERIVED | t1         | ref   | c1,n1_c1_n2   | n1_c1_n2 | 4       | j5.t1.n1          |    1 | Using where; Using index |
      +------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
      

      Good so far

      Now, try EXPLAIN FORMAT=JSON

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "t1",
            "access_type": "index",
            "possible_keys": ["c1", "n1_c1_n2"],
            "key": "n1_c1_n2",
            "key_length": "9",
            "used_key_parts": ["n1", "c1", "n2"],
            "rows": 2,
            "filtered": 100,
            "attached_condition": "t1.c1 <=> 'a' and t1.c1 = 'a'",
            "using_index": true
          },
          "table": {
            "table_name": "<derived2>",
            "access_type": "ref",
            "possible_keys": ["key0"],
            "key": "key0",
            "key_length": "8",
            "used_key_parts": ["n1", "n2"],
            "ref": ["j5.t1.n1", "j5.t1.n2"],
            "rows": 2,
            "filtered": 100,
            "materialized": {
              "query_block": {
                "select_id": 2,
                "table": {
                  "table_name": "t1",
                  "access_type": "ref",
                  "possible_keys": ["c1", "n1_c1_n2"],
                  "key": "n1_c1_n2",
                  "key_length": "4",
                  "used_key_parts": ["n1"],
                  "ref": ["j5.t1.n1"],
                  "rows": 1,
                  "filtered": 100,
                  "attached_condition": "t1.c1 = 'a'",
                  "using_index": true
                }
              }
            }
          }
        }
      }
      

      If one is attentive enough, they can see that "materialized" node has a ref access in it which refers to "j5.t1.n1". Table "t1"
      is outside the subquery, which means the subquery is using LATERAL DERIVED optimization.

      But this is not at all obvious. EXPLAIN FORMAT=JSON should make it clear what optimization was used.

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            psergey Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: