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

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19424 InnoDB's records_in_range estimates are capped at about 50%

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14621 Query very slow compared to Mysql

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21136 InnoDB's records_in_range estimates can be way off

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            Notes from Slack discussion

            psergei Sergei Petrunia added a comment - Notes from Slack discussion this is a known issue: https://bugs.mysql.com/bug.php?id=73386
            psergei Sergei Petrunia added a comment -

            Closing as Duplicate of MDEV-19424.

            psergei Sergei Petrunia added a comment - Closing as Duplicate of MDEV-19424 .

            People

              psergei Sergei Petrunia
              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.