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

Query with order by limit picking index scan over filesort

    XMLWordPrintable

Details

    Description

      The dataset used

      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,
      d int(11) DEFAULT NULL,
      KEY b_3 (a,d),
      KEY b_4 (a,b)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;
      

      MariaDB [test]> select count(*) from t1;
      +----------+
      | count(*) |
      +----------+
      |    10000 |
      +----------+
      

      Now running the query without any limit

      MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b;
      +------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref         | rows | r_rows | filtered | r_filtered | Extra                       |
      +------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
      |    1 | SIMPLE      | t1    | ref  | b_3,b_4       | b_3  | 10      | const,const |  200 | 200.00 |   100.00 |     100.00 | Using where; Using filesort |
      +------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
      

      Now running the query with limit= NUMBER OF TABLE RECORDS

      MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000;
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | r_rows  | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
      |    1 | SIMPLE      | t1    | range | b_3,b_4       | b_4  | 5       | NULL | 2000 | 2000.00 |    10.00 |      10.00 | Using where |
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
      

      This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.

      With limit = 2x table_records

      MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 20000;
      +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref         | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | t1    | index | b_3,b_4       | b_4  | 10      | const,const | 10266 | 10000.00 |     1.95 |       2.00 | Using where |
      +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
      

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              varun Varun Gupta (Inactive)
              Votes:
              1 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.