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

Adding GROUP BY slows down a query

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.3.27, 10.4.17, 10.5.8
    • Fix Version/s: 10.6.0
    • Component/s: Optimizer
    • Labels:

      Description

      Hi,

      this is to report that MariaDB 10.5.8 is also affected by upstream Bug#101838 [1]. Look at the Description, How to repeat and three first comment to understand what I share below (I mixed-up ORDER BY and GROUP BY in the MySQL bug report, I fixed it later for the title, but I cannot fix it for the first comments, sorry about that).

      [1]: https://jira.mariadb.org/browse/MDEV-11852

      Note that I am able also able to reproduce on 10.4.17 and 10.3.27.

      Many thanks for looking into this,

      Jean-François Gagné

      mysql [localhost:10508] {msandbox} (test_jfg) > select version();
      +----------------+
      | version()      |
      +----------------+
      | 10.5.8-MariaDB |
      +----------------+
      1 row in set (0.000 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > show create table p\G
      *************************** 1. row ***************************
             Table: p
      Create Table: CREATE TABLE `p` (
        `p` int(10) unsigned NOT NULL,
        `a` int(10) unsigned NOT NULL,
        `c` bigint(20) unsigned NOT NULL,
        PRIMARY KEY (`p`,`a`,`c`)
      ) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin
      1 row in set (0.000 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > pager cat > /dev/null
      PAGER set to 'cat > /dev/null'
      mysql [localhost:10508] {msandbox} (test_jfg) > select MIN(c) from p where p = @p and a = @a group by p, a;select MIN(c) from p where p = @p and a = @a group by p, a;
      1 row in set (0.282 sec)
       
      1 row in set (0.261 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > select MIN(c) from p where p = @p and a = @a;select MIN(c) from p where p = @p and a = @a;
      1 row in set (0.000 sec)
       
      1 row in set (0.000 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select MIN(c) from p where p = @p and a = @a group by p, a; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.261 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+--------+
      | Variable_name            | Value  |
      +--------------------------+--------+
      | Handler_read_first       | 0      |
      | Handler_read_key         | 1      |
      | Handler_read_last        | 0      |
      | Handler_read_next        | 631283 |
      | Handler_read_prev        | 0      |
      | Handler_read_retry       | 0      |
      | Handler_read_rnd         | 0      |
      | Handler_read_rnd_deleted | 0      |
      | Handler_read_rnd_next    | 0      |
      +--------------------------+--------+
      9 rows in set (0.001 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select MIN(c) from p where p = @p and a = @a; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.000 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_first       | 0     |
      | Handler_read_key         | 1     |
      | Handler_read_last        | 0     |
      | Handler_read_next        | 0     |
      | Handler_read_prev        | 0     |
      | Handler_read_retry       | 0     |
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 0     |
      +--------------------------+-------+
      9 rows in set (0.001 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select p, a, MIN(c) from p where p = @p and a = @a group by p, a; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.271 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+--------+
      | Variable_name            | Value  |
      +--------------------------+--------+
      | Handler_read_first       | 0      |
      | Handler_read_key         | 1      |
      | Handler_read_last        | 0      |
      | Handler_read_next        | 631283 |
      | Handler_read_prev        | 0      |
      | Handler_read_retry       | 0      |
      | Handler_read_rnd         | 0      |
      | Handler_read_rnd_deleted | 0      |
      | Handler_read_rnd_next    | 0      |
      +--------------------------+--------+
      9 rows in set (0.001 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select p, a, MIN(c) from p where p = @p and a = @a; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.255 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+--------+
      | Variable_name            | Value  |
      +--------------------------+--------+
      | Handler_read_first       | 0      |
      | Handler_read_key         | 2      |
      | Handler_read_last        | 0      |
      | Handler_read_next        | 631283 |
      | Handler_read_prev        | 0      |
      | Handler_read_retry       | 0      |
      | Handler_read_rnd         | 0      |
      | Handler_read_rnd_deleted | 0      |
      | Handler_read_rnd_next    | 0      |
      +--------------------------+--------+
      9 rows in set (0.001 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select MIN(c) from p where p = @p and a = @a; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.000 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | Handler_read_first       | 0     |
      | Handler_read_key         | 1     |
      | Handler_read_last        | 0     |
      | Handler_read_next        | 0     |
      | Handler_read_prev        | 0     |
      | Handler_read_retry       | 0     |
      | Handler_read_rnd         | 0     |
      | Handler_read_rnd_deleted | 0     |
      | Handler_read_rnd_next    | 0     |
      +--------------------------+-------+
      9 rows in set (0.001 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > show create table p_\G
      *************************** 1. row ***************************
             Table: p_
      Create Table: CREATE TABLE `p_` (
        `p` int(10) unsigned NOT NULL,
        `a` int(10) unsigned NOT NULL,
        `s` varchar(20) NOT NULL,
        `t` varchar(20) NOT NULL,
        `i` int(10) unsigned DEFAULT NULL,
        `c` bigint(20) unsigned NOT NULL,
        PRIMARY KEY (`p`,`a`,`c`),
        KEY `t` (`t`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
      1 row in set (0.000 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select t, p, a, MIN(c) from p_ where p = @p and a = @a and t = @t group by t, p, a; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.454 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+--------+
      | Variable_name            | Value  |
      +--------------------------+--------+
      | Handler_read_first       | 0      |
      | Handler_read_key         | 318157 |
      | Handler_read_last        | 0      |
      | Handler_read_next        | 631283 |
      | Handler_read_prev        | 0      |
      | Handler_read_retry       | 0      |
      | Handler_read_rnd         | 1      |
      | Handler_read_rnd_deleted | 0      |
      | Handler_read_rnd_next    | 2      |
      +--------------------------+--------+
      9 rows in set (0.001 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select t, p, a, MIN(c) from p_ where p = @p and a = @a and t = @t; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.320 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+--------+
      | Variable_name            | Value  |
      +--------------------------+--------+
      | Handler_read_first       | 0      |
      | Handler_read_key         | 1      |
      | Handler_read_last        | 0      |
      | Handler_read_next        | 631283 |
      | Handler_read_prev        | 0      |
      | Handler_read_retry       | 0      |
      | Handler_read_rnd         | 0      |
      | Handler_read_rnd_deleted | 0      |
      | Handler_read_rnd_next    | 0      |
      +--------------------------+--------+
      9 rows in set (0.001 sec)
       
      mysql [localhost:10508] {msandbox} (test_jfg) > flush status; pager cat > /dev/null; select MIN(c) from p_ where p = @p and a = @a and t = @t; pager; show session status like "Handler_read%";
      Query OK, 0 rows affected (0.000 sec)
       
      PAGER set to 'cat > /dev/null'
      1 row in set (0.262 sec)
       
      Default pager wasn't set, using stdout.
      +--------------------------+--------+
      | Variable_name            | Value  |
      +--------------------------+--------+
      | Handler_read_first       | 0      |
      | Handler_read_key         | 1      |
      | Handler_read_last        | 0      |
      | Handler_read_next        | 631283 |
      | Handler_read_prev        | 0      |
      | Handler_read_retry       | 0      |
      | Handler_read_rnd         | 0      |
      | Handler_read_rnd_deleted | 0      |
      | Handler_read_rnd_next    | 0      |
      +--------------------------+--------+
      9 rows in set (0.001 sec)
      

        Attachments

          Activity

            People

            Assignee:
            varun Varun Gupta (Inactive)
            Reporter:
            jeanfrancois.gagne Jean-François Gagné
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration