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

Optimizer does not use group by optimization with distinct

    XMLWordPrintable

Details

    Description

      This is similar to the following upstream bug that was fixed in MySQL 5.6, but this problem seems to effect all InnoDB tables in MariaDB 10.1, not just partitioned ones:

      https://bugs.mysql.com/bug.php?id=60023

      For example, run the following test:

      CREATE TABLE tbl1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)) ENGINE=InnoDB;
      INSERT INTO tbl1(a) VALUES (1), (2), (3), (4);
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
      INSERT INTO tbl1(a) SELECT a FROM tbl1;
       
      OPTIMIZE TABLE tbl1;
      EXPLAIN SELECT DISTINCT a FROM tbl1;
      SELECT DISTINCT a FROM tbl1;
      EXPLAIN SELECT a FROM tbl1 GROUP BY a;
      SELECT a FROM tbl1 GROUP BY a;
      

      Here's the output seen on MariaDB 10.1.31:

      MariaDB [db1]> EXPLAIN SELECT DISTINCT a FROM tbl1;
      +------+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
      |    1 | SIMPLE      | tbl1  | index | NULL          | a    | 4       | NULL | 65895 | Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+-------+-------------+
      1 row in set (0.00 sec)
       
      MariaDB [db1]> SELECT DISTINCT a FROM tbl1;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      | 4 |
      +---+
      4 rows in set (0.02 sec)
       
      MariaDB [db1]> EXPLAIN SELECT a FROM tbl1 GROUP BY a;
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | tbl1  | range | NULL          | a    | 4       | NULL |    7 | Using index for group-by |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [db1]> SELECT a FROM tbl1 GROUP BY a;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      | 4 |
      +---+
      4 rows in set (0.00 sec)
      

      Compare this to the output seen on 5.6.38:

      mysql> EXPLAIN SELECT DISTINCT a FROM tbl1;
      +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |  1 | SIMPLE      | tbl1  | range | a             | a    | 4       | NULL |    7 | Using index for group-by |
      +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
       
      mysql> SELECT DISTINCT a FROM tbl1;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      | 4 |
      +---+
      4 rows in set (0.00 sec)
       
      mysql> EXPLAIN SELECT a FROM tbl1 GROUP BY a;
      +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |  1 | SIMPLE      | tbl1  | range | a             | a    | 4       | NULL |    7 | Using index for group-by |
      +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
       
      mysql> SELECT a FROM tbl1 GROUP BY a;
      +---+
      | a |
      +---+
      | 1 |
      | 2 |
      | 3 |
      | 4 |
      +---+
      4 rows in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              3 Vote for this issue
              Watchers:
              10 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.