Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
Here is the dataset
create table t1(a int, b int,c int, primary key(a), unique key(b,c)); |
insert into t1 select seq, seq, seq from seq_1_to_10; |
|
|
create table t2(a int, b int,c int); |
insert into t2 select seq, seq, seq+1 from seq_1_to_100; |
MariaDB [test]> EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
|
+------+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
|
| 1 | SIMPLE | t1 | ref | b | b | 10 | test.t2.a,test.t2.b | 1 | Using index |
|
+------+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
|
2 rows in set (0.001 sec)
|
So the key uses to access t1 is key b, which is a UNIQUE key and due to the condition all the columns in the key
are NULL rejecting. So we could use eq_ref access instead of ref access
MariaDB [test]> alter table t1 drop PRIMARY KEY;
|
Query OK, 10 rows affected (0.086 sec)
|
Records: 10 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> alter table t1 add PRIMARY KEY(b,c);
|
Query OK, 0 rows affected (0.055 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100 | Using where |
|
| 1 | SIMPLE | t1 | eq_ref | PRIMARY,b | PRIMARY | 8 | test.t2.a,test.t2.b | 1 | Using index |
|
+------+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+
|
2 rows in set (0.001 sec)
|
If I have a primary key on (b,c), then eq_ref access is picked.
So it would be good if we could use eq_ref access for the first case too!
Attachments
Issue Links
- relates to
-
-
- Closed
-