Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21480

Unique key using ref access though eq_ref access can be used

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: In Progress (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
    • Fix Version/s: 10.3, 10.4, 10.5
    • Component/s: Optimizer
    • Labels:
      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

            Activity

              People

              Assignee:
              varun Varun Gupta
              Reporter:
              varun Varun Gupta
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: