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

Using different index with same range gives different number of records

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.1, 10.2, 10.3, 10.4, 10.5
    • N/A
    • Optimizer
    • None

    Description

      Dataset:

      create table t0 (a int);
      INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);
       
      CREATE TABLE t1 (
      a int(11) DEFAULT NULL,
      b int(11) DEFAULT NULL,
      c int(11) DEFAULT NULL,
      KEY a (a),
      KEY a_b (a,b)
      )engine=MYISAM;
      insert into t1 select A.a , B.a, C.a,D.a from t0 A, t0 B, t0 C, t0 D;
      

      The query is:
      MariaDB [test]> explain select a,b from t1 where a=1 and c> 0 order by b;
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      |    1 | SIMPLE      | t1    | ref  | a,a_b         | a_b  | 5       | const | 1654 | Using where |
      +------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
      1 row in set (0.002 sec)
      

      So we have 2 indexes here on which we can use ref-access,
      1) key a
      2) key a_b
      both support ref access on condition a=1
      Now when i see the output of the estimates for both the index with the range optimizer

      "range_scan_alternatives": [
                            {
                              "index": "a",
                              "ranges": ["(1) <= (a) <= (1)"],
                              "rowid_ordered": true,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 2363,
                              "cost": 3015.4,
                              "chosen": false,
                              "cause": "cost"
                            },
                            {
                              "index": "a_b",
                              "ranges": ["(1) <= (a) <= (1)"],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 1654,
                              "cost": 2128.1,
                              "chosen": false,
                              "cause": "cost"
                            }
                          ]
      

      The estimates for key a and a_b are different while they have the same range.
      The key a has expected rows 2363 while a_b has expected rows as 1654.
      Isn't both the indexes expected to have the same estimates?

      Actual count of records with a=1;

      MariaDB [test]> select count(*) from t1 where a=1;
      +----------+
      | count(*) |
      +----------+
      |     2000 |
      +----------+
      1 row in set (0.018 sec)
      

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.