mysql8-hints-no-views.sql

Trying this on MySQL 8.0.36-debug

create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table twenty(a int);
insert into twenty select a from ten;
insert into twenty select a+10 from ten;

The default join order is:

mysql> explain 
    -> select ten.a from ten, twenty where ten.a=twenty.a limit 1000;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | ten    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL                                       |
|  1 | SIMPLE      | twenty | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


Let's use a hint to force the join order to be twenty-ten instead:

mysql> explain 
    -> select /*+ JOIN_ORDER(twenty,ten) */ ten.a from ten, twenty where ten.a=twenty.a limit 1000;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | twenty | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
|  1 | SIMPLE      | ten    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Then, create a view from this:

mysql> create view v1 as
    -> select /*+ JOIN_ORDER(twenty,ten) */ ten.a from ten, twenty where ten.a=twenty.a limit 1000;
Query OK, 0 rows affected (0.02 sec)

and try selecting from the VIEW and see that the hint is not followed:

mysql> explain select  * from v1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
|  2 | DERIVED     | ten        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL                                       |
|  2 | DERIVED     | twenty     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
3 rows in set, 1 warning (0.00 sec)