Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
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
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)
|
Thank you for the report!
I confirm that combination of where + group by for this cases is slower, on 5.5-10.5:
--source include/have_innodb.inc
--source include/have_sequence.inc
show profiles;
10.2 f924a3bd6cd70c140f00d
set profiling=1;
select MIN(a) from t1 where p = 2 group by p;
MIN(a)
0
select MIN(a) from t1 where p = 2;
MIN(a)
0
select MIN(a) from t1 group by p;
MIN(a)
0
show profiles;
Query_ID Duration Query
1 0.00159900 select MIN(a) from t1 where p = 2 group by p
2 0.00025713 select MIN(a) from t1 where p = 2
3 0.00032281 select MIN(a) from t1 group by p
analyze format=json select MIN(a) from t1 where p = 2 group by p;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 1.0317,
"table": {
"table_name": "t1",
"access_type": "ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["p"],
"ref": ["const"],
"r_loops": 1,
"rows": 999,
"r_rows": 1001,
"r_total_time_ms": 0.6489,
"filtered": 100,
"r_filtered": 100,
"using_index": true
}
}
}
analyze format=json select MIN(a) from t1 where p = 2;
ANALYZE
{
"query_block": {
"select_id": 1,
"table": {
"message": "Select tables optimized away"
}
}
}
analyze format=json select MIN(a) from t1 group by p;
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.0393,
"table": {
"table_name": "t1",
"access_type": "range",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["p"],
"r_loops": 1,
"rows": 2,
"r_rows": 1,
"r_total_time_ms": 0.0133,
"filtered": 100,
"r_filtered": 100,
"using_index_for_group_by": true
}
}
}