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

Using different index with same range gives different number of records

    Details

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

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

                Dates

                • Created:
                  Updated: