Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
The dataset used
create table t0 (a int); |
INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0); |
 |
CREATE TABLE t1 ( |
a int(11) DEFAULT NULL, |
b int(11) DEFAULT NULL, |
d int(11) DEFAULT NULL, |
KEY b_3 (a,d), |
KEY b_4 (a,b) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; |
insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D; |
MariaDB [test]> select count(*) from t1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 10000 |
|
+----------+
|
Now running the query without any limit
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b;
|
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
|
| 1 | SIMPLE | t1 | ref | b_3,b_4 | b_3 | 10 | const,const | 200 | 200.00 | 100.00 | 100.00 | Using where; Using filesort |
|
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
|
Now running the query with limit= NUMBER OF TABLE RECORDS
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000;
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
|
| 1 | SIMPLE | t1 | range | b_3,b_4 | b_4 | 5 | NULL | 2000 | 2000.00 | 10.00 | 10.00 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
|
This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.
With limit = 2x table_records
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 20000;
|
+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
|
| 1 | SIMPLE | t1 | index | b_3,b_4 | b_4 | 10 | const,const | 10266 | 10000.00 | 1.95 | 2.00 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
|
Attachments
Issue Links
- relates to
-
MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
-
- Stalled
-
-
-
- Stalled
-