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

LP:1002146 - Unneeded filesort when executing a GROUP BY query

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.3.11
    • Component/s: None
    • Labels:

      Description

      If to create tables t1 and t2 in MariaDB 5.5 as with the following commands

      CREATE TABLE t1 (
      col_int_key INT,
      pk INT,
      PRIMARY KEY (pk),
      KEY (col_int_key)
      ) ENGINE=INNODB;
      INSERT INTO t1 VALUES (2,3),(3,2),(3,5),(4,6);
      CREATE TABLE t2 (
      col_int_key INT,
      pk INT,
      PRIMARY KEY (pk),
      KEY (col_int_key)
      ) ENGINE=INNODB;
      INSERT INTO t2 VALUES (0,9),(3,10),(4,6),(6,1),(100,3),(200,5);

      and

      set join_cache_level=0;

      then the execution plan for the query

      SELECT t2.col_int_key AS field1
      FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key
      WHERE t2.pk < 7 AND t2.col_int_key <> 7
      GROUP BY field1;

      will use unneeded filesort:

      MariaDB [test]> explain SELECT t2.col_int_key AS field1 FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.t_key WHERE t2.pk < 7 AND t2.col_int_key <> 7 GROUP BY field1;
      ---------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ---------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE t2 range col_int_key col_int_key 5 NULL 5 Using where; Usinx; Using temporary; Using filesort
      1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index

      ---------------------------------------------------------------------------------------------------------------------+

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: