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

Optimizer does not use group by optimization with distinct

    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

              • Assignee:
                varun Varun Gupta
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                3 Vote for this issue
                Watchers:
                10 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: