gistfile1.txt

mysql>  analyze format=json 
    -> WITH RECURSIVE t AS
    -> (
    ->   /*500 rows instantly */
    ->   SELECT u1.user, u1.host, d1.db, u1.select_priv, 
    ->          IF(u1.password <> '', u1.password, u1.authentication_string) AS password, 
    ->          u1.is_role, u1.default_role
    ->     FROM user AS u1
    ->     LEFT JOIN db AS d1
    ->       ON (u1.user = d1.user AND u1.host = d1.host)
    ->    WHERE u1.plugin IN ('', 'mysql_native_password')
    -> UNION
    ->   SELECT u.user, u.host, t.db, u.select_priv, IF(u.password <> '', u.password, u.authentication_string), u.is_role, u.default_role
    ->     FROM user AS u
    ->     LEFT JOIN tables_priv AS t
    ->       ON (u.user = t.user AND u.host = t.host)
    ->    WHERE u.plugin IN ('', 'mysql_native_password')
    -> )
    -> select * from t;


{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 144.1,
    "table": {
      "table_name": "<derived2>",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 2055,
      "r_rows": 860,
      "r_total_time_ms": 15.431,
      "r_extra_time_ms": 6.9024,
      "filtered": 100,
      "r_filtered": 100,
      "materialized": {
        "query_block": {
          "union_result": {
            "table_name": "<union2,3>",
            "access_type": "ALL",
            "r_loops": 1,
            "r_rows": 860,
            "query_specifications": [
              {
                "query_block": {
                  "select_id": 2,
                  "r_loops": 1,
                  "r_total_time_ms": 101.22,
                  "table": {
                    "table_name": "u1",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 411,
                    "r_rows": 411,
                    "r_total_time_ms": 0.6846,
                    "r_extra_time_ms": 0.6794,
                    "filtered": 100,
                    "r_filtered": 100,
                    "attached_condition": "u1.`plugin` in ('','mysql_native_password')"
                  },
                  "table": {
                    "table_name": "d1",
                    "access_type": "ref",
                    "possible_keys": ["PRIMARY", "User"],
                    "key": "PRIMARY",
                    "key_length": "180",
                    "used_key_parts": ["Host"],
                    "ref": ["j1.u1.Host"],
                    "r_loops": 411,
                    "rows": 4,
                    "r_rows": 81.625,
                    "r_total_time_ms": 44.144,
                    "r_extra_time_ms": 55.597,
                    "filtered": 100,
                    "r_filtered": 1.3175,
                    "attached_condition": "trigcond(d1.`User` = u1.`User`)",
                    "using_index": true
                  }
                }
              },
              {
                "query_block": {
                  "select_id": 3,
                  "operation": "UNION",
                  "r_loops": 1,
                  "r_total_time_ms": 19.279,
                  "table": {
                    "table_name": "u",
                    "access_type": "ALL",
                    "r_loops": 1,
                    "rows": 411,
                    "r_rows": 411,
                    "r_total_time_ms": 0.3722,
                    "r_extra_time_ms": 0.3896,
                    "filtered": 100,
                    "r_filtered": 100,
                    "attached_condition": "u.`plugin` in ('','mysql_native_password')"
                  },
                  "table": {
                    "table_name": "t",
                    "access_type": "ref",
                    "possible_keys": ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "180",
                    "used_key_parts": ["Host"],
                    "ref": ["j1.u.Host"],
                    "r_loops": 411,
                    "rows": 1,
                    "r_rows": 5.2214,
                    "r_total_time_ms": 3.6358,
                    "r_extra_time_ms": 14.865,
                    "filtered": 100,
                    "r_filtered": 1.1184,
                    "attached_condition": "trigcond(t.`User` = u.`User`)",
                    "using_index": true
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
}