Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.1
Description
create table ten(a int primary key); |
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
create table t1(a int, b int); |
insert into t1 select a,a from ten; |
alter table t1 add index(a); |
analyze table t1; |
create view v1 as select * from t1; |
We make a query that
- uses t1 directly. This usage has t1.a<3 condition.
- uses t1 through v1. This usage has no conditions.
First, run the query without hints. The view gets merged:
explain select * from v1,t1 where t1.a< 3; |
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+ |
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | Using index condition | |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer (flat, BNL join) | |
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+ |
NO_INDEX(t1 a) refers to the t1 at the top level. Proof: possible_keys is now NULL everywhere and the range access is gone:
explain extended select /*+ NO_INDEX(t1 a) */ * from v1,t1 where t1.a< 3; |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | | |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (flat, BNL join) | |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ |
2 rows in set, 1 warning (0.002 sec) |
 |
Note (Code 1003): select /*+ NO_INDEX(`t1`@`select#1` `a`) */ `j22`.`t1`.`a` AS `a`,`j22`.`t1`.`b` AS `b`,`j22`.`t1`.`a` AS `a`,`j22`.`t1`.`b` AS `b` from `j22`.`t1` join `j22`.`t1` where `j22`.`t1`.`a` < 3 |
|
JOIN_PREFIX refers to the t1 inside v1. Proof: the first table is the t1 without restrictions. Note that it was the second when the query was ran without hints:
explain extended select /*+ JOIN_PREFIX(t1) */ * from v1,t1 where t1.a< 3; |
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------------------------------+ |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | | |
| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 3 | Using index condition; Using where; Using join buffer (flat, BNL join) | |
+------+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------------------------------------+ |
2 rows in set, 1 warning (0.002 sec) |
 |
Note (Code 1003): select /*+ NO_INDEX(`t1`@`select#1` `a`) */ `j22`.`t1`.`a` AS `a`,`j22`.`t1`.`b` AS `b`,`j22`.`t1`.`a` AS `a`,`j22`.`t1`.`b` AS `b` from `j22`.`t1` join `j22`.`t1` where `j22`.`t1`.`a` < 3 |
This looks rather confusing.
NOTE: We need to decide what to do about this, don't jump to code something.
Checking on MySQL 9.4.0
It's the same:
mysql> explain select * from v1,t1 where t1.a< 3;
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------------------------+
|
| 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 3 | 100.00 | Using index condition |
|
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using join buffer (hash join) |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------------------------+
|
2 rows in set, 1 warning (0.01 sec)
|
mysql> explain select /*+ NO_INDEX(t1 a) */ * from v1,t1 where t1.a< 3;
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
|
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 33.33 | Using where |
|
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using join buffer (hash join) |
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|
mysql> explain select /*+ JOIN_PREFIX(t1) */ * from v1,t1 where t1.a< 3;
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------------------+
|
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |
|
| 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 3 | 100.00 | Using index condition; Using join buffer (hash join) |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|