Using EXPLAIN INTO and EXPLAIN FOR SCHEMA in MySQL 8.1 and 8.2

Starting with MySQL 8.1, the EXPLAIN statement supports an INTO clause, allowing the JSON‑formatted output of an EXPLAIN query to be stored directly in a user variable instead of being sent to the client. The variable can then be treated as a JSON object, enabling programmatic extraction of specific fields such as the query cost using JSON_EXTRACT .

Example:

mysql> EXPLAIN FORMAT=JSON INTO @explain_output SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id;

After execution, the variable @explain_output contains the full JSON output. To retrieve only the cost, run:

SELECT JSON_EXTRACT(@explain_output, "$.query_block.cost_info.query_cost") AS query_cost;

In MySQL 8.2, a new FOR SCHEMA clause was added to EXPLAIN, enabling the execution of EXPLAIN on tables that reside in a different schema than the current one. This makes it possible to write a stored procedure that runs EXPLAIN for each schema and collects statistics automatically.

Sample procedure to explain a query for a given schema:

DELIMITER |
CREATE PROCEDURE explain_query_for_schema(IN schema_name VARCHAR(64), IN query VARCHAR(1000))
BEGIN
  SET @explain_stmt = CONCAT('EXPLAIN FORMAT=JSON INTO @explain_output FOR SCHEMA ', schema_name, ' ', query);
  PREPARE stmt FROM @explain_stmt;
  EXECUTE stmt;
  INSERT INTO explain_outputs(schema_name, query, explain_output) VALUES (schema_name, query, @explain_output);
END |
DELIMITER ;

A second procedure iterates over all schemas, calling the first one for each:

CREATE PROCEDURE explain_query_for_all_schemas(IN query VARCHAR(1000))
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE schema_name VARCHAR(64);
  DECLARE cur_schema_names CURSOR FOR SELECT schema_name FROM schema_name_table;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur_schema_names;
  explain_loop: LOOP
    FETCH cur_schema_names INTO schema_name;
    IF done THEN LEAVE explain_loop; END IF;
    CALL explain_query_for_schema(schema_name, query);
  END LOOP;
  CLOSE cur_schema_names;
END;

Using these procedures, you can collect the cost of a given query across multiple schemas and store the results in a table for further analysis. Sample output shows schema names, the original query, the extracted cost, and timestamps.

The two extensions, EXPLAIN INTO and EXPLAIN FOR SCHEMA, were instrumental in the author’s own project MySQL Autopilot Indexing , where they were used to gather data that drives automatic index recommendations.

For more details, refer to the official MySQL EXPLAIN documentation.