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

EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.9
    • 10.0.11
    • None

    Description

      Prepare the dataset:

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t1 (
        kp1 int, kp2 int, 
        filler1 char(100),
        filler2 char(100),
        key(kp1, kp2)
      );
       
      insert into t1 
      select 
        A.a,
        B.a,
        'filler-data-1',
        'filler-data-2'
      from ten A, ten B, ten C;
      set histogram_size=100;
      set use_stat_tables='preferably';
      set optimizer_use_condition_selectivity=4;
      analyze table t1 persistent for all;

      Now, let's try a ref access. Let's start without ref(const):

      explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=ten.a+1;
      +------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref            | rows | filtered | Extra                 |
      +------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+
      |    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL           |   10 |   100.00 | Using where           |
      |    1 | SIMPLE      | t1    | ref  | kp1           | kp1  | 10      | j19.ten.a,func |   10 |   100.00 | Using index condition |
      +------+-------------+-------+------+---------------+------+---------+----------------+------+----------+-----------------------+

      So, ref access will give us 10 rows (on every index lookup). Ok.

      explain extended select * from ten, t1 where t1.kp1=ten.a and t1.kp2=4;
      +------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref             | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+
      |    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL            |   10 |   100.00 | Using where |
      |    1 | SIMPLE      | t1    | ref  | kp1           | kp1  | 10      | j19.ten.a,const |   10 |     9.90 |             |
      +------+-------------+-------+------+---------------+------+---------+-----------------+------+----------+-------------+

      This one seems to be wrong. ref access still produces 10 rows, but then filtered=9.90% , which is what selectivity would be if we weren't using ref access.

      Indeed, if we disable ref access:

      explain extended select * from ten, t1 ignore index(kp1) where t1.kp1=ten.a and t1.kp2=4;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
      |    1 | SIMPLE      | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 |                                                 |
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |     9.90 | Using where; Using join buffer (flat, BNL join) |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

      we get selectivity=9.90%.

      Attachments

        Activity

          People

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