In this post, I am sharing one of the 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 EXPLAIN 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 to complete, but using EXPLAIN FOR CONNECTION we can check the reason of 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,
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
You can also visit this article to find and kill running connections of MySQL.
The syntax :
EXPLAIN [options] FOR CONNECTION connection_id;
Once you execute this command and the result is empty because there are no any explainable statements. If we have SELECT, INSERT, UPDATE and DELETE, we can get execution information using this option.
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
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.