simple-query-default-jcl.txt

ANALYZE FORMAT=JSON
WITH RECURSIVE users AS
(
  SELECT t.user, t.host, t.db, t.select_priv, t.password, t.default_role AS role
    FROM t_mat as t
   WHERE t.is_role <> 'Y'
UNION
  SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
    FROM t_mat as t
    JOIN users AS u ON (t.user = u.role)
    LEFT JOIN roles_mapping AS r ON (t.user = r.user)
)
SELECT t2.user, t2.host, t2.db, t2.select_priv, t2.password
  FROM users AS t2
 WHERE t2.user <> 'root';


ANALYZE: {
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 5743.4,
    "table": {
      "table_name": "<derived2>",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 860,
      "r_rows": 8623,
      "r_table_time_ms": 195.27,
      "r_other_time_ms": 50.489,
      "filtered": 100,
      "r_filtered": 99.965,
      "attached_condition": "t2.`user` <> 'root'",
      "materialized": {
        "query_block": {
          "recursive_union": {
            "table_name": "<union2,3>",
            "access_type": "ALL",
            "r_loops": 0,
            "r_rows": null,
            "query_specifications": [
              {
                "query_block": {
                  "select_id": 2,
                  "r_loops": 1,
                  "r_total_time_ms": 86.823,
                  "table": {
                    "table_name": "t",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 860,
                    "r_rows": 860,
                    "r_table_time_ms": 24.452,
                    "r_other_time_ms": 62.33,
                    "filtered": 100,
                    "r_filtered": 84.419,
                    "attached_condition": "t.is_role <> 'Y'"
                  }
                }
              },
              {
                "query_block": {
                  "select_id": 3,
                  "operation": "UNION",
                  "r_loops": 3,
                  "r_total_time_ms": 5185.6,
                  "table": {
                    "table_name": "t",
                    "access_type": "ALL",
                    "r_loops": 3,
                    "rows": 860,
                    "r_rows": 860,
                    "r_table_time_ms": 38.234,
                    "r_other_time_ms": 2.2282,
                    "filtered": 100,
                    "r_filtered": 100
                  },
                  "table": {
                    "table_name": "<derived2>",
                    "access_type": "ref",
                    "possible_keys": ["key0"],
                    "key": "key0",
                    "key_length": "241",
                    "used_key_parts": ["role"],
                    "ref": ["test.t.user"],
                    "r_loops": 2580,
                    "rows": 10,
                    "r_rows": 7.9802,
                    "r_table_time_ms": 431.08,
                    "r_other_time_ms": 107.71,
                    "filtered": 100,
                    "r_filtered": 100
                  },
                  "block-nl-join": {
                    "table": {
                      "table_name": "r",
                      "access_type": "index",
                      "key": "Host",
                      "key_length": "660",
                      "used_key_parts": ["Host", "User", "Role"],
                      "r_loops": 7,
                      "rows": 177,
                      "r_rows": 177,
                      "r_table_time_ms": 2.4479,
                      "r_other_time_ms": 4825.8,
                      "filtered": 100,
                      "r_filtered": 100,
                      "using_index": true
                    },
                    "buffer_type": "flat",
                    "buffer_size": "256Kb",
                    "join_type": "BNL",
                    "attached_condition": "trigcond(r.`User` = t.`user`)",
                    "r_filtered": 0.7788
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
}
1 row in set (5.75 sec)