Syntax\n------\n\nSHOW EXPLAIN [FORMAT=JSON] FOR <connection_id>;\nEXPLAIN [FORMAT=JSON] FOR CONNECTION <connection_id>;\n\nDescription\n-----------\n\nThe SHOW EXPLAIN command allows one to get an EXPLAIN (that is, a description of a query plan) of a query running in a certain connection.\n\n``sql\nSHOW EXPLAIN FOR <connection_id>;\n`\n\nwill produce an EXPLAIN output for the query that connection number connection_id is running. The connection id can be obtained with SHOW PROCESSLIST.\n\n`sql\nSHOW EXPLAIN FOR 1;\n+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+\n| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |\n+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+\n|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |\n+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+\n1 row in set, 1 warning (0.00 sec)\n`\n\nThe output is always accompanied with a warning which shows the query the target connection is running (this shows what the EXPLAIN is for):\n\n`sql\nSHOW WARNINGS;\n+-------+------+------------------------+\n| Level | Code | Message                |\n+-------+------+------------------------+\n| Note  | 1003 | select sum(a) from tbl |\n+-------+------+------------------------+\n1 row in set (0.00 sec)\n`\n\nEXPLAIN FOR CONNECTION\n\nThe EXPLAIN FOR CONNECTION syntax was added for MySQL compatibility.\n\nFORMAT=JSON\n\nSHOW EXPLAIN [FORMAT=JSON] FOR <connection_id> extends SHOW EXPLAIN to return more detailed JSON output.\n\nEXPLAIN FOR CONNECTION and FORMAT=JSON are not available.\n\nPossible Errors\n\nThe output can be only produced if the target connection is _currently_ running a query, which has a ready query plan. If this is not the case, the output will be:\n\n`sql\nSHOW EXPLAIN FOR 2;\nERROR 1932 (HY000): Target is not running an EXPLAINable command\n`\n\nYou will get this error when:\n\n The target connection is not running a command for which one can run EXPLAIN;\n The target connection is running a command for which one can run EXPLAIN, but there is no query plan yet (for example, tables are open and locks are acquired before the query plan is produced).\n\nDifferences Between SHOW EXPLAIN and EXPLAIN Outputs\n\nBackground\n\nIn MySQL, EXPLAIN execution takes a slightly different route from the way the real query (typically the SELECT) is optimized. This is unfortunate, and has caused a number of bugs in EXPLAIN. (For example, see MDEV-326, MDEV-410, and lp:1013343.lp:992942 is not directly about EXPLAIN, but it also would not have existed if MySQL didn't try to delete parts of a query plan in the middle of the query)\n\nSHOW EXPLAIN examines a running SELECT, and hence its output may be slightly different from what EXPLAIN SELECT would produce. We did our best to make sure that either the difference is negligible, or SHOW EXPLAIN's output is closer to reality than EXPLAIN's output.\n\nList of Recorded Differences\n\n SHOW EXPLAIN may have Extra='no matching row in const table', where EXPLAIN would produce Extra='Impossible WHERE ...'\n For queries with subqueries, SHOW EXPLAIN may print select_type==PRIMARY where regular EXPLAIN used to print select_type==SIMPLE, or vice versa.\n\nRequired Permissions\n\nRunning SHOW EXPLAIN` requires the same permissions as running SHOW PROCESSLIST would.\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-explain