gistfile1.txt

analyze format=json 
with recursive users AS
(
  SELECT t_mat.user, t_mat.host, t_mat.db, t_mat.select_priv, t_mat.password, t_mat.default_role AS role
    FROM t_mat
   WHERE t_mat.is_role <> 'Y'
UNION
  SELECT u.user, u.host, t_mat.db, t_mat.select_priv, u.password, r.role
    FROM t_mat
    JOIN users AS u ON (t_mat.user = u.role)
    LEFT JOIN roles_mapping AS r ON (t_mat.user = r.user)
)
SELECT DISTINCT t_mat.user, t_mat.host, t_mat.db, t_mat.select_priv, t_mat.password
  FROM users AS t_mat
 WHERE t_mat.user <> 'root';

| {
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 5865.7,
    "temporary_table": {
      "table": {
        "table_name": "<derived2>",
        "access_type": "ALL",
        "r_loops": 1,
        "rows": 860,
        "r_rows": 8623,
        "r_total_time_ms": 186.12,
        "r_extra_time_ms": 356.07,
        "filtered": 100,
        "r_filtered": 99.965,
        "attached_condition": "t_mat.`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": 81.086,
                    "table": {
                      "table_name": "t_mat",
                      "access_type": "ALL",
                      "r_loops": 1,
                      "rows": 860,
                      "r_rows": 860,
                      "r_total_time_ms": 20.377,
                      "r_extra_time_ms": 60.694,
                      "filtered": 100,
                      "r_filtered": 84.419,
                      "attached_condition": "t_mat.is_role <> 'Y'"
                    }
                  }
                },
                {
                  "query_block": {
                    "select_id": 3,
                    "operation": "UNION",
                    "r_loops": 3,
                    "r_total_time_ms": 5039.7,
                    "table": {
                      "table_name": "t_mat",
                      "access_type": "ALL",
                      "r_loops": 3,
                      "rows": 860,
                      "r_rows": 860,
                      "r_total_time_ms": 36.901,
                      "r_extra_time_ms": 2.3242,
                      "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": ["j1.t_mat.user"],
                      "r_loops": 2580,
                      "rows": 10,
                      "r_rows": 7.9802,
                      "r_total_time_ms": 399.31,
                      "r_extra_time_ms": 102.91,
                      "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_total_time_ms": 2.5413,
                        "r_extra_time_ms": 4696.9,
                        "filtered": 100,
                        "r_filtered": 100,
                        "using_index": true
                      },
                      "buffer_type": "flat",
                      "buffer_size": "256Kb",
                      "join_type": "BNL",
                      "attached_condition": "trigcond(r.`User` = t_mat.`user`)",
                      "r_filtered": 0.7788
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
} |