Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Currently, Index Condition Pushdown (and Rowid Filtering) cannot be used with reverse-ordered index scans.
Search for IndexConditionPushdownAndReverseScans, RowidFilteringAndReverseScans in sql/sql_select.cc for detailed explanation why.
See prepare_for_reverse_ordered_access() where we disable these features when switching to reverse scans.
This task is about enabling ICP (and maybe Rowid Filtering) to work with reverse-ordered scans.
re MySQL 8
MySQL 8 actually supports ICP + Reverse scans, see handler::set_end_range call. Maybe we should adopt this, or maybe something something similar.
Some more details about how they support it: Support added by this patch:
https://github.com/mysql/mysql-server/commit/da1d92fd46071cd86de61058b6ea39fd9affcd87
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 t10 (a int, b int, c int, key(a,b)); |
insert into t10 select a,a,a from one_k; |
|
|
explain select * from t10 force index(a) where a between 10 and 20 and b+1 <3333 order by a desc, b desc; |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
|
| 1 | SIMPLE | t10 | NULL | range | a | a | 5 | NULL | 1 | 100.00 | Using index condition; Backward index scan |
|
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------+
|
As pointed out by igor, there is some interesting limitation: reverse index scan+ICP doesn't work with ref access:
mysql> explain select * from t10 force index(a) where a=10 and b+1 <3333 order by a desc, b desc;
|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------+
|
| 1 | SIMPLE | t10 | NULL | ref | a | a | 5 | const | 1 | 100.00 | Using where; Backward index scan |
|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+----------------------------------+
|
mysql> explain select * from t10 force index(a) where a=10 and b+1 <3333 order by a asc, b asc;
|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|
| 1 | SIMPLE | t10 | NULL | ref | a | a | 5 | const | 1 | 100.00 | Using index condition |
|
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
|
1 row in set, 1 warning (0.01 sec)
|
code-wise, it is here:
void QEP_TAB::push_index_cond(const JOIN_TAB *join_tab, uint keyno, |
Opt_trace_object *trace_obj) {
|
JOIN *const join_ = join(); |
DBUG_TRACE;
|
|
|
ASSERT_BEST_REF_IN_JOIN_ORDER(join_);
|
assert(join_tab == join_->best_ref[idx()]); |
|
|
if (join_tab->reversed_access) // @todo: historical limitation, lift it! |
return; |
Attachments
Issue Links
- relates to
-
MDEV-33875 ORDER BY DESC causes ROWID Filter optimization performance degradation
-
- Closed
-