Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
The following 3 queries should have identical query plans (only using an index)
create table t1 (pk int primary key, a int, b int, key(a,b));
|
insert into t1 with recursive cte(a) as (select 1 as a union select a+1 from cte where a <100) select a, a*10, a*100 from cte;
|
explain select a, b, count(a) from t1 group by a, b order by a desc;
|
explain select a, b, count(a) from t1 group by a desc, b order by a desc;
|
explain select a, b, count(a) from t1 group by a desc, b desc order by a desc;
|
MariaDB [test]> explain select a, b, count(a) from t1 group by a, b order by a desc;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 10 | NULL | 100 | Using index; Using temporary; Using filesort |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> explain select a, b, count(a) from t1 group by a desc, b order by a desc;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 10 | NULL | 100 | Using index; Using temporary; Using filesort |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> explain select a, b, count(a) from t1 group by a desc, b desc order by a desc;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | index | NULL | a | 10 | NULL | 100 | Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|