[MDEV-24353] Adding GROUP BY slows down a query - Jira

Details

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

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

Transition	Time In Source Status	Execution Times

Alice Sherepa made transition - 2020-12-07 11:17

Open

Confirmed

16h 10m

Varun Gupta (Inactive) made transition - 2020-12-07 14:50

Confirmed

In Progress

3h 32m

Varun Gupta (Inactive) made transition - 2020-12-09 14:05

In Progress

In Review

1d 23h 14m

Sergei Petrunia made transition - 2020-12-10 16:28

In Review

Stalled

1d 2h 23m

Varun Gupta (Inactive) made transition - 2020-12-11 13:15

Stalled

Closed

20h 47m

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Jean-François Gagné

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2020-12-06 19:07

Updated:: 2020-12-15 11:46

Resolved:: 2020-12-11 13:15

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server