|
create table ten(a int primary key);
|
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table one_k(a int primary key);
|
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
|
|
explain
|
select * from one_k where a > 1000 + (select max(a) from ten where ten.a<one_k.a);
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | PRIMARY | one_k | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
|
| 2 | DEPENDENT SUBQUERY | ten | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
Now, run this:
set optimizer_trace=1;
|
select * from one_k where a > 1000 + (select max(a) from ten where ten.a<one_k.a);
|
select * from information_schema.optimizer_trace\G
|
And see lots of these in the trace:
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
"join_execution": {
|
"select_id": 2,
|
"steps": []
|
}
|
Do they have any value?
|