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

Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 and use_stat_tables= PREFERABLY

    Details

      Description

      MariaDB [test]> CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [test]> INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
          (2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
      Query OK, 15 rows affected (0.01 sec)
      Records: 15  Duplicates: 0  Warnings: 0
      

      Case 1:

      MariaDB [test]> set @@optimizer_use_condition_selectivity=4;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> set @@use_stat_tables= PREFERABLY;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> explain  SELECT 1 FROM t1 AS t1_outer WHERE 
          -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
      | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref             | rows | Extra                    |
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
      |    1 | PRIMARY      | t1_outer    | index  | a             | a            | 10      | NULL            |   16 | Using where; Using index |
      |    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | test.t1_outer.a |    1 |                          |
      |    2 | MATERIALIZED | t1          | range  | NULL          | a            | 5       | NULL            |    5 | Using index for group-by |
      +------+--------------+-------------+--------+---------------+--------------+---------+-----------------+------+--------------------------+
      3 rows in set (0.01 sec)
      

      Case 2:

      MariaDB [test]> set @@optimizer_use_condition_selectivity=1;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> set @@use_stat_tables= NEVER;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> explain  SELECT 1 FROM t1 AS t1_outer WHERE 
          -> a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2); 
      +------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
      | id   | select_type  | table       | type  | possible_keys | key  | key_len | ref                | rows | Extra                    |
      +------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
      |    1 | PRIMARY      | <subquery2> | ALL   | distinct_key  | NULL | NULL    | NULL               |    4 |                          |
      |    1 | PRIMARY      | t1_outer    | ref   | a             | a    | 5       | <subquery2>.max(b) |    4 | Using index              |
      |    2 | MATERIALIZED | t1          | range | NULL          | a    | 5       | NULL               |    4 | Using index for group-by |
      +------+--------------+-------------+-------+---------------+------+---------+--------------------+------+--------------------------+
      3 rows in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                varun Varun Gupta
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: