gistfile1.txt

set join_cache_level=6;

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';

...
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 2310.1,
    "table": {
      "table_name": "<derived2>",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 860,
      "r_rows": 8623,
      "r_table_time_ms": 189.27,
      "r_other_time_ms": 49.469,
      "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": 80.904,
                  "table": {
                    "table_name": "t",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 860,
                    "r_rows": 860,
                    "r_table_time_ms": 27.642,
                    "r_other_time_ms": 53.217,
                    "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": 1777.7,
                  "table": {
                    "table_name": "t",
                    "access_type": "ALL",
                    "r_loops": 3,
                    "rows": 860,
                    "r_rows": 860,
                    "r_table_time_ms": 36.451,
                    "r_other_time_ms": 2.1278,
                    "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": 419.73,
                    "r_other_time_ms": 129.22,
                    "filtered": 100,
                    "r_filtered": 100
                  },
                  "block-nl-join": {
                    "table": {
                      "table_name": "r",
                      "access_type": "hash_index",
                      "key": "#hash#$hj:Host",
                      "key_length": "240:660",
                      "used_key_parts": ["User"],
                      "ref": ["test.t.user"],
                      "r_loops": 8,
                      "rows": 177,
                      "r_rows": 177,
                      "r_table_time_ms": 1.5963,
                      "r_other_time_ms": 1398.1,
                      "filtered": 100,
                      "r_filtered": 100,
                      "using_index": true
                    },
                    "buffer_type": "flat",
                    "buffer_size": "256Kb",
                    "join_type": "BNLH",
                    "attached_condition": "trigcond(r.`User` = t.`user`)",
                    "r_filtered": 333.56
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
} |
1 row in set (2.32 sec)