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?
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.