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

The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.3.16, 10.4.6
    • Optimizer
    • None

    Description

      When the optimizer considers a ref access for a full PRIMARY key, it will use an estimate of rows=1. The same will happen for a UNIQUE key, but only if the columns are not NULL-able. If any of the columns are NULLable, the estimate from index statistics is used.

      The reason is possible NULL values. A unique index with NULL-able columns may have multiple rows with NULL values.

      However, the most common type of join uses null-rejecting predicates, "keypart = ...". For those, NULLable UNIQUE index is the same as non-NULLable, and the optimizer should be able to figure out that it should use an estimate of rows=1.

      Testcase:

      create table ten(a int);
      		 
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      		 
      create table t1 (
      		 
        pk int not null primary key auto_increment,
      		 
        a int,
      		 
        b int,
      		 
        unique key(a)
      		 
      );
      		 
      # 10K of null values
      		 
      insert into t1 (a,b) select null, 12345 from ten A, ten B, ten C;
      		 
      # 10 non-null values 
      insert into t1 (a,b) select a,a from ten;
      		 
      analyze table t1;


      mysql> explain select * from ten,t1 where ten.a=t1.a;
      		 
      +------+-------------+-------+------+---------------+------+---------+----------+------+-------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref      | rows | Extra       |
      		 
      +------+-------------+-------+------+---------------+------+---------+----------+------+-------------+
      		 
      |    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL     |   10 | Using where |
      		 
      |    1 | SIMPLE      | t1    | ref  | a             | a    | 5       | j5.ten.a |   45 |             |
      		 
      +------+-------------+-------+------+---------------+------+---------+----------+------+-------------+

      note, t1.rows=45 here.
      Even if we use a null-rejecting ref access on a UNIQUE key, and will get at most 1 row.

      Index statistics:

      mysql> show keys from t1;
      		 
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      		 
      | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      		 
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      		 
      | t1    |          0 | PRIMARY  |            1 | pk          | A         |        1010 |     NULL | NULL   |      | BTREE      |         |               |
      		 
      | t1    |          0 | a        |            1 | a           | A         |          22 |     NULL | NULL   | YES  | BTREE      |         |               |
      		 
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      (If you want to suggest to fiddle with innodb_stats_method, check out MDEV-19574 - persistent innodb stats will always use nulls_equal)
      (If you want to suggest to use EITS, note that collecting index stats with EITS currently requires a full index scan, so we can't assume EITS is always there ATM).

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21480 Unique key using ref access though eq_ref access can be used

          • Major - Major loss of function.
          • Closed

          Activity

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              1 Vote for this issue
              Watchers:
              4 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.