Backward Index Scan

Constraints

This feature is only available when the kernel version is 2.0.48.231200 or later.

Enabling Backward Index Scan

You can also use hints to enable or disable Backward Index Scan. The syntax is as follows:

• Enabling Backward Index Scan during SQL statement execution

  /*+ set_var(optimizer_switch='backward_index_scan=on') */ :

• Disabling Backward Index Scan during SQL statement execution

  /*+ set_var(optimizer_switch='backward_index_scan=off') */ :

Example

1. Enable Backward Index Scan.
   • Set the switch value in the optimizer_switch parameter.

     mysql> set optimizer_switch='backward_index_scan=on';
     Query OK, 0 rows affected (0.00 sec)
     
     mysql> set optimizer_switch='backward_index_scan=off';
     Query OK, 0 rows affected (0.00 sec)

   • Use hints to set the switch value in SQL statements.

     mysql> explain select /*+ set_var(optimizer_switch='backward_index_scan=on') */ c13,c16 from tt where c10=10 and c7=7 and c12=12 and to_days(c13)=547864 and c16 is not null order by c13 desc;
     
     mysql> explain select /*+ set_var(optimizer_switch='backward_index_scan=off') */ c13,c16 from tt where c10=10 and c7=7 and c12=12 and to_days(c13)=547864 and c16 is not null order by c13 desc;

2. Check the control effect.

   Run the EXPLAIN statement to check whether the execution plan contains Backward Index Scan.

   1. Prepare data.

      create table tt(
        id int not null primary key,
        a int,
        b int,
        c int,
        key idx_a_b(a, b));
      insert into tt values(1,1,1,1),(2,1,2,1),(3,2,3,2),(4,2,4,3),(5,2,4,4);

   2. When Backward Index Scan is enabled, the optimizer selects this feature to eliminate sorting. Query the optimizer_switch parameter to determine whether this feature is enabled.

      mysql> select @@optimizer_switch\G
      *************************** 1. row ***************************
      @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on,derived_merge_no_subquery_check=off,gen_col_partition_prune=off,partial_result_cache=off,offset_pushdown=off,backward_index_scan=on
      1 row in set (0.00 sec)
      
      mysql> explain select * from tt where a = 2 order by b desc;
      +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+
      | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra               |
      +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+
      |  1 | SIMPLE      | tt    | NULL       | ref  | idx_a_b       | idx_a_b | 5       | const |    3 |   100.00 | Backward index scan |
      +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+
      1 row in set, 1 warning (0.00 sec)

   3. When Backward Index Scan is disabled, the optimizer adds the Sort operator for sorting. Check the following execution plan.

      mysql> set optimizer_switch='backward_index_scan=off';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> explain select * from tt where a = 2 order by b desc;
      +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
      | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra          |
      +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
      |  1 | SIMPLE      | tt    | NULL       | ref  | idx_a_b       | idx_a_b | 5       | const |    3 |   100.00 | Using filesort |
      +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+----------------+
      1 row in set, 1 warning (0.00 sec)

Performance Test

When an SQL statement is executed, the optimizer uses Backward Index Scan. The query takes about 4.54s.

mysql> explain analyze select detail_record_id, record_id, business_id, business_detail_id, unique_code, create_time, creator, last_updater, last_update_time, tenant_id, is_usable, operation_time, detail_operation_type, work_time, operator_id from detail_record d where d.tenant_id=554008 and d.creator = 585764 and operation_type = 3 and to_days(operation_time) = to_days(now()) and detail_operation_type is not null order by operation_time desc limit 1\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 1 row(s)  (cost=151707.54 rows=1) (actual time=4539.137..4539.137 rows=0 loops=1)
    -> Filter: ((d.creator = 585764) and (to_days(d.operation_time) = <cache>(to_days(now()))) and (d.detail_operation_type is not null))  (cost=151707.54 rows=263565) (actual time=4539.135..4539.135 rows=0 loops=1)
        -> Index lookup on d using idx_time (tenant_id=554008, operation_type=3; iterate backwards)  (cost=151707.54 rows=2928502) (actual time=0.089..4449.445 rows=1562755 loops=1)
1 row in set (4.54 sec)

After hints are used to prevent the optimizer from using Backward Index Scan, there is no incompatibility issue between Backward Index Scan and index condition pushdown in this scenario. As a result, the query time is reduced to approximately 0.37s, and the execution efficiency is significantly improved.

mysql> explain analyze select /*+ set_var(optimizer_switch='backward_index_scan=off') */ detail_record_id, record_id, business_id, business_detail_id, unique_code, create_time, creator, last_updater, last_update_time, tenant_id, is_usable, operation_time, detail_operation_type, work_time, operator_id from detail_record d where d.tenant_id=554008 and d.creator = 585764 and operation_type = 3 and to_days(operation_time) = to_days(now()) and detail_operation_type is not null order by operation_time desc limit 1\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 1 row(s)  (cost=209431.59 rows=1) (actual time=370.208..370.208 rows=0 loops=1)
    -> Sort: d.operation_time DESC, limit input to 1 row(s) per chunk  (cost=209431.59 rows=2928502) (actual time=370.207..370.207 rows=0 loops=1)
        -> Filter: ((d.creator = 585764) and (d.detail_operation_type is not null))  (actual time=370.189..370.189 rows=0 loops=1)
            -> Index lookup on d using idx_time (tenant_id=554008, operation_type=3), with index condition: (to_days(d.operation_time) = <cache>(to_days(now())))  (actual time=370.188..370.188 rows=0 loops=1)
1 row in set (0.37 sec)