CREATE TABLE t1 (pk1 INT PRIMARY KEY, f INT) ENGINE=Aria;

INSERT INTO t1 VALUES (1,0),(2,0);

CREATE TABLE t2 (pk2 INT PRIMARY KEY) ENGINE=Aria;

INSERT INTO t2 VALUES (1),(2),(3);

CREATE VIEW v2 AS SELECT pk2, COUNT(*) AS cnt FROM t2 GROUP BY pk2;

MariaDB [test]> ANALYZE FORMAT=JSON SELECT * FROM t1 INNER JOIN v2 ON pk1 = pk2 WHERE f <> 5;

| {

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 1.1568,

    "table": {

      "table_name": "t1",

      "access_type": "ALL",

      "possible_keys": ["PRIMARY"],

      "r_loops": 1,

      "rows": 2,

      "r_rows": 2,

      "r_total_time_ms": 0.0677,

      "filtered": 100,

      "r_filtered": 100,

      "attached_condition": "t1.f <> 5"

    },

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "4",

      "used_key_parts": ["pk2"],

      "ref": ["test.t1.pk1"],

      "r_loops": 2,

      "rows": 2,

      "r_rows": 1,

      "r_total_time_ms": 0.7043,

      "filtered": 100,

      "r_filtered": 100,

      "materialized": {

        "query_block": {

          "select_id": 2,

          "r_loops": 2,

          "r_total_time_ms": 0.8569,

          "outer_ref_condition": "t1.pk1 is not null",

          "table": {

            "table_name": "t2",

            "access_type": "eq_ref",

            "possible_keys": ["PRIMARY"],

            "key": "PRIMARY",

            "key_length": "4",

            "used_key_parts": ["pk2"],

            "ref": ["test.t1.pk1"],

            "r_loops": 2,

            "rows": 1,

            "r_rows": 1,

            "r_total_time_ms": 0.0723,

            "filtered": 100,

            "r_filtered": 100,

            "using_index": true

          }

        }

      }

    }

  }

} |