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

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

                Dates

                Created:
                Updated:
                Resolved: