Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.1, 5.5.29, 5.1.67, 5.2.14
-
None
Description
In many cases, when there is no index compatible with the group list of a query over one table, and, at the same time, a range condition over an index can be extracted from the WHERE clause, the optimizer chooses the execution plan over this range rather than a table scan plan as soon as the query uses a limit clause.
Without limit clause the optimizer chooses a table scan if it turns out to
be cheaper than the range scan.
The problem can be demonstrated on the following test case.
Create and populate table t1 with following commands:
create table t1 (
|
pk int primary key auto_increment, b int, c int, index (b)
|
);
|
|
|
insert into t1(b,c) values
|
(1,10), (2,20), (3,30), (4,40), (5,50), (6,60), (7,70), (8,80), (9,90);
|
insert into t1(b,c) select b,c from t1;
|
insert into t1(b,c) select b,c from t1;
|
insert into t1(b,c) select b,c from t1;
|
insert into t1(b,c) select b,c from t1;
|
Then you'll see with any MariaDB version/release that the query
select c from t1 where b != 5 group by c;
|
is executed with a table scan,
while the query
select c from t1 where b != 5 group by c limit 2;
|
is executed with a range scan that is very inefficient here:
MariaDB [test]> explain select c from t1 where b != 5 group by c;
|
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | b | NULL | NULL | NULL | 144 | Using where; Using temporary; Using filesort |
|
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|
|
|
|
|
MariaDB [test]> explain select c from t1 where b != 5 group by c limit 2;
|
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | range | b | b | 5 | NULL | 136 | Using where; Using temporary; Using filesort |
|
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|