If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE FOR CONNECTION’” in MySQL, it’s probably because you’re trying to use the FOR CONNECTION option with EXPLAIN ANALYZE, but EXPLAIN ANALYZE doesn’t support the FOR CONNECTION option.

It’s true that we can use the FOR CONNECTION option with the EXPLAIN statement, we can’t use it when using the ANALYZE variant of EXPLAIN (we can use EXPLAIN with or without ANALYZE). At least, this is how it works as of this writing (MySQL 8.3).

To fix, either remove the ANALYZE keyword from the statement or don’t use the FOR CONNECTION option.

Example of Error

Here’s an example of code that produces the error:

EXPLAIN ANALYZE FOR CONNECTION 98;

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE FOR CONNECTION'

We get an error because we can’t use FOR CONNECTION when using the ANALYZE option of EXPLAIN.

Solution 1

We can still run EXPLAIN against a connection. All we need to do is remove the ANALYZE keyword:

EXPLAIN FOR CONNECTION 98;

Result:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

This time we got a result without error.

To set up this example, I ran SELECT SLEEP(20) in one connection, then jumped to another connection and ran SHOW PROCESSLIST; in order to get the connection identifier, then ran EXPLAIN FOR CONNECTION for that connection (which was connection 98).

Solution 2

Another option we have is to keep the ANALYZE keyword, but remove the FOR CONNECTION option. Obviously doing this means that we don’t get the information based on the connection. But if we know what statement the connection is currently running, then we could apply that statement directly to the EXPLAIN ANALYZE statement.

For example, let’s say the connection is selecting all columns from the nicer_but_slower_film_list view (from the sakila sample database). We can run the following to analyse that statement:

EXPLAIN ANALYZE 
SELECT * FROM nicer_but_slower_film_list;

Result:

 -> Table scan on nicer_but_slower_film_list  (cost=2.5..2.5 rows=0) (actual time=48.2..48.4 rows=997 loops=1)
    -> Materialize  (cost=0..0 rows=0) (actual time=48.2..48.2 rows=997 loops=1)
        -> Group aggregate: group_concat(tmp_field separator ', ')  (actual time=40.7..46.6 rows=997 loops=1)
            -> Sort: film.film_id, category.`name`  (actual time=40.7..43.5 rows=5462 loops=1)
                -> Stream results  (cost=3172 rows=5478) (actual time=0.152..32.9 rows=5462 loops=1)
                    -> Nested loop inner join  (cost=3172 rows=5478) (actual time=0.131..21.6 rows=5462 loops=1)
                        -> Nested loop inner join  (cost=1255 rows=5478) (actual time=0.123..12.2 rows=5462 loops=1)
                            -> Nested loop inner join  (cost=456 rows=1000) (actual time=0.113..5.42 rows=1000 loops=1)
                                -> Nested loop inner join  (cost=106 rows=1000) (actual time=0.0895..0.853 rows=1000 loops=1)
                                    -> Table scan on category  (cost=1.85 rows=16) (actual time=0.0462..0.0771 rows=16 loops=1)
                                    -> Filter: (film_category.film_id is not null)  (cost=0.648 rows=62.5) (actual time=0.0187..0.0417 rows=62.5 loops=16)
                                        -> Covering index lookup on film_category using fk_film_category_category (category_id=category.category_id)  (cost=0.648 rows=62.5) (actual time=0.0185..0.0343 rows=62.5 loops=16)
                                -> Single-row index lookup on film using PRIMARY (film_id=film_category.film_id)  (cost=0.25 rows=1) (actual time=0.00427..0.00432 rows=1 loops=1000)
                            -> Covering index lookup on film_actor using idx_fk_film_id (film_id=film_category.film_id)  (cost=0.251 rows=5.48) (actual time=0.00375..0.00596 rows=5.46 loops=1000)
                        -> Single-row index lookup on actor using PRIMARY (actor_id=film_actor.actor_id)  (cost=0.25 rows=1) (actual time=0.00141..0.00146 rows=1 loops=5462)

So rather than use FOR CONNECTION for that connection, I simply entered the SQL statement that the connection was running.