[MDEV-21480] Unique key using ref access though eq_ref access can be used Created: 2020-01-15  Updated: 2020-05-05  Resolved: 2020-05-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.3.23, 10.4.13, 10.5.3

Type: Bug Priority: Major
Reporter: Varun Gupta (Inactive) Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-19600 The optimizer should be able to produ... Closed

 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!

 Comments   
Comment by Varun Gupta (Inactive) [ 2020-04-21 ]

The optimizer trace for the plan t2 and t1 looks like

              {
 
                "plan_prefix": [],
 
                "table": "t2",
 
                "best_access_path": {
 
                  "considered_access_paths": [
 
                    {
 
                      "access_type": "scan",
 
                      "resulting_rows": 100,
 
                      "cost": 2.3174,
 
                      "chosen": true
 
                    }
 
                  ],
 
                  "chosen_access_method": {
 
                    "type": "scan",
 
                    "records": 100,
 
                    "cost": 2.3174,
 
                    "uses_join_buffering": false
 
                  }
 
                },
 
                "rows_for_plan": 100,
 
                "cost_for_plan": 22.317,
 
                "rest_of_plan": [
 
                  {
 
                    "plan_prefix": ["t2"],
 
                    "table": "t1",
 
                    "best_access_path": {
 
                      "considered_access_paths": [
 
                        {
 
                          "access_type": "eq_ref",
 
                          "index": "b",
 
                          "rows": 1,
 
                          "cost": 100,
 
                          "chosen": true
 
                        },

As we can see during the planner phase we pick eq_ref access but during the function create_ref_for_key we pick the access to be REF access

Comment by Varun Gupta (Inactive) [ 2020-04-21 ]

Patch
http://lists.askmonty.org/pipermail/commits/2020-April/014248.html

Comment by Sergei Petrunia [ 2020-04-30 ]

Ok to push.

Generated at Thu Feb 08 09:07:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.