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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • 5.3.11
    • None

    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

          Ascending order - Click to sort in descending order
          igor Igor Babaev (Inactive) created issue -
          ratzpo Rasmus Johansson (Inactive) added a comment -

          Launchpad bug id: 1002146

          ratzpo Rasmus Johansson (Inactive) added a comment - Launchpad bug id: 1002146
          ratzpo Rasmus Johansson (Inactive) made changes -
          Field Original Value New Value
          Labels Launchpad
          ratzpo Rasmus Johansson (Inactive) made changes -
          Fix Version/s Maria 5.5 [ 11303 ]
          Labels Launchpad Launchpad MariaDB_5.5
          ratzpo Rasmus Johansson (Inactive) made changes -
          Key IMT-6564 MDEV-645
          Project ImportTest [ 10200 ] MariaDB Development [ 10000 ]
          Workflow jira [ 20213 ] defaullt [ 21489 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.29 [ 11701 ]
          serg Sergei Golubchik made changes -
          Labels Launchpad MariaDB_5.5 Launchpad
          igor Igor Babaev (Inactive) added a comment -

          The same problem can be observed on mariadb-5.5 (rev 3574)with 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.col_int_key <> 7
          GROUP BY field1;

          when the engine is changed for MYISAM:

          MariaDB [test]> ALTER TABLE t1 ENGINE=MYISAM;
          Query OK, 4 rows affected (0.03 sec)
          Records: 4 Duplicates: 0 Warnings: 0

          MariaDB [test]> ALTER TABLE t2 ENGINE=MYISAM;
          Query OK, 6 rows affected (0.02 sec)
          Records: 6 Duplicates: 0 Warnings: 0

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

          id select_type table type possible_keys key key_len ref rows Extra

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

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

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

          igor Igor Babaev (Inactive) added a comment - The same problem can be observed on mariadb-5.5 (rev 3574)with 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.col_int_key <> 7 GROUP BY field1; when the engine is changed for MYISAM: MariaDB [test] > ALTER TABLE t1 ENGINE=MYISAM; Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [test] > ALTER TABLE t2 ENGINE=MYISAM; Query OK, 6 rows affected (0.02 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [test] > explain SELECT t2.col_int_key AS field1 FROM t2 USE INDEX(col_int_key) STRAIGHT_JOIN t1 ON t2.col_int_key WHERE t2.col_int_key <> 7 GROUP BY field1; ----- ----------- ----- ----- ------------- ----------- ------- ---- ---- ----------------------------------------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ----------- ------- ---- ---- ----------------------------------------------------------+ 1 SIMPLE t2 index col_int_key col_int_key 5 NULL 6 Using where; Using index; Using temporary; Using filesort 1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index ----- ----------- ----- ----- ------------- ----------- ------- ---- ---- ----------------------------------------------------------+
          igor Igor Babaev (Inactive) added a comment -

          The bug is reproducible in mariadb-5.3 as well.

          igor Igor Babaev (Inactive) added a comment - The bug is reproducible in mariadb-5.3 as well.
          igor Igor Babaev (Inactive) added a comment -

          The fix was pushed into mariadb-5.3 (rev 3604)

          igor Igor Babaev (Inactive) added a comment - The fix was pushed into mariadb-5.3 (rev 3604)
          igor Igor Babaev (Inactive) made changes -
          Fix Version/s 5.3.11 [ 11700 ]
          Fix Version/s 5.5.29 [ 11701 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 21489 ] MariaDB v2 [ 46286 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 46286 ] MariaDB v3 [ 66960 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 66960 ] MariaDB v4 [ 145030 ]

          People

            igor Igor Babaev (Inactive)
            igor Igor Babaev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.