MySQL 5.7: Introduced EXPLAIN FOR CONNECTION to check the Execution Plan of Running Connections

In this post, I am sharing a new feature of MySQL 5.7 which is EXPLAIN FOR CONNECTION.
As we all aware about the EXPLAIN command of MySQL Server, which we are using to find a query execution information.

EXPLAIN FOR CONNECTION is a new feature and using this we can find query execution information about a running query connection.

When we are diagnosing performance problems, then this is very useful for us.
For example, We are running a statement in one session which is taking a long time, but using EXPLAIN FOR CONNECTION we can check the reason for the delay in another session.

We need to pass connection_id to check the running connection information.
We can check connection_id using different method like:

1 2 3 4 5

SHOW PROCESSLIST;   SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;   SELECT CONNECTION_ID();


You can also visit this article to find and kill the running connections of MySQL. 

The syntax :

1	EXPLAIN [options] FOR CONNECTION connection_id;

Once you execute above command and if result is empty, there are no any explainable statements. If we have SELECT, INSERT, UPDATE and DELETE, we can get execution information using this option.

For example:

1 2 3 4 5 6 7 8 9 10 11

mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ |            8254 | +-----------------+ 1 row in set (0.00 sec)   mysql> EXPLAIN FOR CONNECTION 8254; ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE