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

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.3.23, 10.4.13, 10.5.3
    • Optimizer
    • 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

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.