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

Estimates are low for a condition that satisfies all the rows of an INNODB table

    XMLWordPrintable

    Details

      Description

      Example: a table with 50K rows. All rows have a1=1. However, ha_innobase::records_in_range() will return about 25K to the optimizer.

      This may cause a bad query plan. The impact of this is bigger in recent versions as they use E(rows) from the potential range accesses as a source of data about condition selectivity.

      --source include/have_sequence.inc
      --source include/have_innodb.inc
       
      CREATE TABLE t1 (id int PRIMARY KEY, a1 int, KEY (a1))engine=innodb;
      INSERT INTO t1 SELECT seq FROM seq_1_to_50000;
      
      

      MariaDB [test]> explain select * FROM t1 force index(a1) where a1=1;
      +------+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra       |
      +------+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
      |    1 | SIMPLE      | t1    | ref  | a1            | a1   | 5       | const | 24485 | Using index |
      +------+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
      

      MariaDB [test]> select count(*) from t1 where a1=1;
      +----------+
      | count(*) |
      +----------+
      |    50000 |
      +----------+
      

      I would expect the estimates to be more closer for such cases.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated: