Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.6
Description
The optimization of MDEV-30660 is performed too late to affect some of the optimizer choices:
create table t1 ( |
pk1 int, |
pk2 int, |
filler char(100), |
primary key(pk1, pk2) |
);
|
insert into t1 select seq, seq, seq from seq_1_to_10000; |
MariaDB [test]> explain select count(distinct pk1) from t1 group by pk2 order by null;
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 8 | NULL | 10030 | Using index; Using filesort |
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+-----------------------------+
|
1 row in set (0.000 sec)
|
Optimizer Trace shows that DISTINCT was removed:
{
|
"prepare_sum_aggregators": {
|
"function": "count(distinct t1.pk1)",
|
"aggregator_type": "simple"
|
}
|
}
|
but note the "Using filesort" in EXPLAIN output^^.
It is present, because the GROUP BY optimization was preparing to use COUNT-DISTINCT which requires that one GROUP BY group is processed after another...
If I remove DISTINCT manually, the query won't use sorting:
MariaDB [test]> explain select count(pk1) from t1 group by pk2 order by null;
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 8 | NULL | 10030 | Using index; Using temporary |
|
+------+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-