Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL)
-
None
Description
Test data
create table ten(a int); |
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
|
create table one_k(a int); |
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; |
|
create table t1(a int, b int); |
insert into t1 select A.a + B.a* 10, A.a + B.a* 10 from ten A, ten B; |
create table t2(a int, b int, key(a)); |
insert into t2 select A.a + B.a* 10, A.a+B.a*10 from ten A, ten B; |
create table t3(a int, b int); |
insert into t3 select A.a + B.a* 10 + C.a * 100, A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C |
MariaDB [test]> explain select * from t1,t2 where t1.a > 95 and t1.a=t2.a order by t2.a;
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using filesort |
|
| 1 | SIMPLE | t2 | ref | a | a | 5 | test.t1.a | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+-----------------------------+
|
2 rows in set (0.008 sec)
|
|
MariaDB [test]> explain select * from t1,t2 where t1.a > 95 and t1.a=t2.a order by t2.a+1;
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using temporary; Using filesort |
|
| 1 | SIMPLE | t2 | ref | a | a | 5 | test.t1.a | 1 | |
|
+------+-------------+-------+------+---------------+------+---------+-----------+------+----------------------------------------------+
|
2 rows in set (0.004 sec)
|
So for expressions we don't propagate equal fields and so we are not able to use the optimization where we can sort by the first table
Attachments
Issue Links
- relates to
-
MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
- Stalled