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

ORDER BY limit optimizer doesn't take condition selectivity into account

    XMLWordPrintable

Details

    Description

      ORDER BY limit optimizer doesn't take condition selectivity into account

      Test dataset:

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      create table t10 (
        key1 int,
        col1 int,
        filler1 char(32),
        key(key1)
      );
       
      insert into t10 select 
        A.a + 1000*B.a,
        MOD(A.a + 1000*B.a, 10000),
        'filler-data'
      from 
        one_k A, one_k B;
       
      set use_stat_tables=preferably;
      set histogram_size=100;
      set optimizer_use_condition_selectivity=4;
      analyze table t10 persistent for all;

      Then, run:

      explain extended select * from t10 where col1=1000111 order by key1 desc limit 10 ;
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+
      |    1 | SIMPLE      | t10   | index | NULL          | key1 | 5       | NULL |   10 |   100.00 | Using where |
      +------+-------------+-------+-------+---------------+------+---------+------+------+----------+-------------+

      It's going to scan the entire index. Note that filtered=100%, even if "col1=1000111" is very selective.
      Let's check that other parts of the optimizer are aware of this:

      MariaDB [j12]> explain extended select * from t10 where col1=1000111;
      +------+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+
      |    1 | SIMPLE      | t10   | ALL  | NULL          | NULL | NULL    | NULL | 1000000 |     0.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+---------+----------+-------------+

      Attachments

        Issue Links

          Activity

            People

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