Details
-
Technical task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5
-
None
Description
In the function best_access_path, where we try to find the best way to read a tables (index[range, ref, scan] and table scan)
Current code but does not consider that a particular access method considered can achieve ordering.
To consider this we would need to check for all the indexes of the table that is ordering achieved by them or not. We do something similar in test_for_cheaper_ordering, where we go through all the indexes of the first non-const table and check if we can achieve the ordering. If the ordering can be satisfied then we consider range or index scan on the given index.
I think we can also think of adding this to best_access_path. With such cases we would not need to add of costing and we can shortcut for limit from the point onwards.
An example of this is the test case of mdev-8306
The query is
select * from |
t_fact
|
join dim1 on t_fact.dim1_id= dim1.dim1_id |
join dim2 on t_fact.dim2_id= dim2.dim2_id |
order by |
t_fact.col1
|
limit 1000;
|
BAD PLAN
 |
+------+-------------+--------+--------+-----------------+---------+---------+-------------------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+--------+-----------------+---------+---------+-------------------+------+---------------------------------+
|
| 1 | SIMPLE | dim1 | ALL | PRIMARY | NULL | NULL | NULL | 500 | Using temporary; Using filesort |
|
| 1 | SIMPLE | t_fact | ref | dim1_id,dim2_id | dim1_id | 4 | j3.dim1.dim1_id | 1 | |
|
| 1 | SIMPLE | dim2 | eq_ref | PRIMARY | PRIMARY | 4 | j3.t_fact.dim2_id | 1 | |
|
+------+-------------+--------+--------+-----------------+---------+---------+-------------------+------+---------------------------------+
|
GOOD PLAN
+------+-------------+--------+--------+-----------------+---------+---------+-------------------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+--------+-----------------+---------+---------+-------------------+------+-------+
|
| 1 | SIMPLE | t_fact | index | dim1_id,dim2_id | col1 | 4 | NULL | 1000 | |
|
| 1 | SIMPLE | dim1 | eq_ref | PRIMARY | PRIMARY | 4 | j3.t_fact.dim1_id | 1 | |
|
| 1 | SIMPLE | dim2 | eq_ref | PRIMARY | PRIMARY | 4 | j3.t_fact.dim2_id | 1 | |
|
+------+-------------+--------+--------+-----------------+---------+---------+-------------------+------+-------+
|
The BAD PLAN takes a very long time to execute , it does the full join and then applies the limit
While in the GOOD PLAN(we apply STRAIGHT JOIN to get the best order), we pick the index that achieves the ordering and then shortcut it for limit