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

Poor execution plan for single-table grouping query with limit clause

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.0.1, 5.5.29, 5.1.67, 5.2.14
    • 10.1
    • None

    Description

      In many cases, when there is no index compatible with the group list of a query over one table, and, at the same time, a range condition over an index can be extracted from the WHERE clause, the optimizer chooses the execution plan over this range rather than a table scan plan as soon as the query uses a limit clause.
      Without limit clause the optimizer chooses a table scan if it turns out to
      be cheaper than the range scan.

      The problem can be demonstrated on the following test case.

      Create and populate table t1 with following commands:

      create table t1 (
      pk int primary key auto_increment, b int, c int, index (b)
      );
       
      insert into t1(b,c) values
      (1,10), (2,20), (3,30), (4,40), (5,50), (6,60), (7,70), (8,80), (9,90);
      insert into t1(b,c) select b,c from t1;
      insert into t1(b,c) select b,c from t1;
      insert into t1(b,c) select b,c from t1;
      insert into t1(b,c) select b,c from t1;

      Then you'll see with any MariaDB version/release that the query

        select c from t1 where b != 5 group by c;

      is executed with a table scan,
      while the query

        select c from t1 where b != 5 group by c limit 2;

      is executed with a range scan that is very inefficient here:

      MariaDB [test]> explain select c from t1 where b != 5 group by c;
      +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
      | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
      +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
      |  1 | SIMPLE      | t1    | ALL  | b             | NULL | NULL    | NULL |  144 | Using where; Using temporary; Using filesort |
      +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
       
       
      MariaDB [test]> explain select c from t1 where b != 5 group by c limit 2;
      +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
      | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
      +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
      |  1 | SIMPLE      | t1    | range | b             | b    | 5       | NULL |  136 | Using where; Using temporary; Using filesort |
      +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            igor Igor Babaev
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.