gistfile1.txt

create table users_t_mat as
select 
  t.user, t.db, t.select_priv, r.role
from
  t_mat as t
  LEFT JOIN roles_mapping AS r ON (t.user = r.user);
Query OK, 1448 rows affected (0.42 sec)
Records: 1448  Duplicates: 0  Warnings: 0

analyze format=json
WITH RECURSIVE users AS
(
  SELECT t.user, t.host, t.db, t.select_priv, t.default_role AS role
    FROM t_mat as t
   WHERE t.is_role <> 'Y'
UNION
  SELECT u.user, u.host, ut.db, ut.select_priv, ut.role
  FROM 
    users AS u, users_t_mat as ut 
  where (ut.user = u.role)
)
SELECT t2.user, t2.host, t2.db, t2.select_priv
  FROM users AS t2
 WHERE t2.user <> 'root';

mysql> analyze format=json WITH RECURSIVE users AS (   SELECT t.user, t.host, t.db, t.select_priv, t.default_role AS role     FROM t_mat as t    WHERE t.is_role <> 'Y' UNION   SELECT u.user, u.host, ut.db, ut.select_priv, ut.role   FROM      users AS u, users_t_mat as ut    where (ut.user = u.role) ) SELECT t2.user, t2.host, t2.db, t2.select_priv   FROM users AS t2  WHERE t2.user <> 'root'\G
*************************** 1. row ***************************
ANALYZE: {
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 553.59,
    "table": {
      "table_name": "<derived2>",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 860,
      "r_rows": 8623,
      "r_table_time_ms": 3.3747,
      "r_other_time_ms": 12.607,
      "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": 47.455,
                  "table": {
                    "table_name": "t",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 860,
                    "r_rows": 860,
                    "r_table_time_ms": 33.664,
                    "r_other_time_ms": 13.749,
                    "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": 475.88,
                  "table": {
                    "table_name": "ut",
                    "access_type": "ALL",
                    "r_loops": 3,
                    "rows": 1448,
                    "r_rows": 1448,
                    "r_table_time_ms": 52.701,
                    "r_other_time_ms": 2.7684,
                    "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.ut.user"],
                    "r_loops": 4344,
                    "rows": 10,
                    "r_rows": 6.5338,
                    "r_table_time_ms": 154.6,
                    "r_other_time_ms": 279.44,
                    "filtered": 100,
                    "r_filtered": 100
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
}
1 row in set (0.56 sec)