Incomplete execution plan in MySQL 8

Question

I wanted to get the execution plan of a query in MySQL 8.0 but it gives me an incomplete plan.

Edited on Sep 8, 2018:

With the effort of showing a simplified example, the original query ended up with subqueries that didn't retrieve any rows. It seems MySQL's optimizer simplifies those queries to the point it fully prunes parts of the query. I modified the query to get data on the subqueries. Here's the example:

create table branch (
  id int primary key not null,
  name varchar(30) not null
);
insert into branch (id, name) values (101, 'California');
insert into branch (id, name) values (102, 'Ohio');
insert into branch (id, name) values (103, 'Delaware');

create table account (
  id int primary key not null auto_increment,
  balance int
);
insert into account (id, balance) values (1001, 120);
insert into account (id, balance) values (1004, 500);
insert into account (id, balance) values (1005, 45);

create table transaction (
  tx_id int primary key not null auto_increment,
  account_id int not null,
  amount int not null,
  branch_id int references branch (id)
);
insert into transaction (account_id, amount, branch_id) values
  (1001,  10, 101),
  (1001, 150, 101),
  (1001, 200, 101),
  (1001, -70, 102),
  (1001, -20, 102),
  (1001,-150, 102),
  (1004,  50, 103),
  (1004, 300, 101),
  (1004, 150, 102),
  (1005, 100, 102),
  (1005, -55, 101);

The query now is:

explain
select *
from account a
  join transaction t4 on t4.account_id = a.id
  join branch b5 on b5.id = t4.branch_id
  join (select account_id as account_id from transaction t7 where amount > 0) t6
    on t6.account_id = a.id
  where a.balance < 7 * (
    select avg(amount) from transaction t
      join branch b on b.id = t.branch_id
      where t.account_id = a.id
        and b.name in (select name from branch b7
                       where name like '%a%')
  )
  and a.balance < 5 * (
    select max(amount)
      from transaction t2
      join branch b2 on b2.id = t2.branch_id
      where b2.name not in (select name from branch b8
                            where name like '%i%')
  );

It now shows (traditional plan):

id select_type         table type   key     key_len ref rows filtered Extra
-- ------------------- ----- ------ ------- ------- --- ---- -------- -----
1  PRIMARY             a     ALL                        3    33.33    Using where
1  PRIMARY             t7    ALL                        11   9.09     Using where
1  PRIMARY             t4    ALL                        11   10       Using where
1  PRIMARY             b5    eq_ref PRIMARY 4       ... 1    100      
5  SUBQUERY            b2    ALL                        3    100      Using where
5  SUBQUERY            t2    ALL                        11   10       Using where
6  DEPENDENT SUBQUERY  b8    ALL                        3    33.33    Using where
3  DEPENDENT SUBQUERY  b7    ALL                        3    33.33    Using where
3  DEPENDENT SUBQUERY  t     ALL                        11   10       Using where
3  DEPENDENT SUBQUERY  b     eq_ref PRIMARY 4       ... 1    33.33    Using where

It now shows information for all the tables except for the scalar subquery t6. Where is it?

Answer 1

I tried testing your query, but I had zero rows in any of the tables. The EXPLAIN shows "Impossible WHERE noticed after reading const tables" which means there are no rows that satisfy the query conditions.

In my test, I see t2, b2, b8, t, b, b7, t7, but not a, t4, b5, t6. It seems to omit tables from the EXPLAIN if they won't be read because the query conditions mean there's no point in reading them, because they are guaranteed to match no rows.

I don't see any logical purpose to this clause:

join (select max(account_id) as account_id from transaction t7) t6 
    on t6.account_id = a.id

If I take this join out of the query, I get an EXPLAIN without the "Impossible WHERE" note, and it has all the other correlation names:

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                             |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------------+
|  1 | PRIMARY            | a     | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 | Using where                                                       |
|  1 | PRIMARY            | t4    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | PRIMARY            | b5    | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  4 | SUBQUERY           | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL                                                              |
|  4 | SUBQUERY           | b2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  5 | DEPENDENT SUBQUERY | b8    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                                       |
|  2 | DEPENDENT SUBQUERY | t     | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                                       |
|  2 | DEPENDENT SUBQUERY | b     | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where; Using join buffer (Block Nested Loop)                |
|  2 | DEPENDENT SUBQUERY | b7    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; FirstMatch(b); Using join buffer (Block Nested Loop) |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------------------------+

I didn't create any indexes as I guessed at your tables, so this EXPLAIN shows no optimization. But at least all the correlation names appear.