[Commits] de442635810: MDEV-26996: Support descending indexes in the range optimizer
revision-id: de4426358101575648e968b4cdae35527da74b23 (mariadb-10.6.1-249-gde442635810) parent(s): c95270df0bc0ba4c93eeb17f078ac85dfd5f5c1b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-12-14 16:39:37 +0300 message: MDEV-26996: Support descending indexes in the range optimizer - Code cleanup - Disable "Using index for GROUP BY" over indexes with DESC keyparts --- mysql-test/main/desc_index_range.result | 25 ++++++++++- mysql-test/main/desc_index_range.test | 13 +++++- mysql-test/main/opt_trace.result | 52 +++++++++++----------- mysql-test/main/opt_trace_index_merge.result | 8 ++-- .../main/opt_trace_index_merge_innodb.result | 8 ++-- sql/opt_range.cc | 23 +++++----- 6 files changed, 82 insertions(+), 47 deletions(-) diff --git a/mysql-test/main/desc_index_range.result b/mysql-test/main/desc_index_range.result index 53a608fe2d9..feec5dc1720 100644 --- a/mysql-test/main/desc_index_range.result +++ b/mysql-test/main/desc_index_range.result @@ -154,5 +154,28 @@ json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) "(4,80) <= (a,b) <= (2,50)" ] ] -set optimizer_trace=default; drop table t2; +# +# Check that "Using index for group-by" is disabled (it's not supported, yet) +# +CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc)); +insert into t1 select 2,seq from seq_0_to_1000; +EXPLAIN select MIN(a) from t1 where p = 2 group by p; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1000 Using index +select json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) +[ + + [ + + { + "index": "PRIMARY", + "usable": false, + "cause": "Reverse-ordered (not supported yet)" + } + ] +] +drop table t1; +set optimizer_trace=default; diff --git a/mysql-test/main/desc_index_range.test b/mysql-test/main/desc_index_range.test index 94d6b76258d..bcb9ce83318 100644 --- a/mysql-test/main/desc_index_range.test +++ b/mysql-test/main/desc_index_range.test @@ -73,5 +73,16 @@ select * from t2 where a between 2 and 4 and b between 50 and 80; select json_detailed(json_extract(trace, '$**.range_access_plan.ranges')) from information_schema.optimizer_trace; -set optimizer_trace=default; drop table t2; + +--echo # +--echo # Check that "Using index for group-by" is disabled (it's not supported, yet) +--echo # +CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a desc)); +insert into t1 select 2,seq from seq_0_to_1000; +EXPLAIN select MIN(a) from t1 where p = 2 group by p; +select json_detailed(json_extract(trace, '$**.potential_group_range_indexes')) +from information_schema.optimizer_trace; +drop table t1; + +set optimizer_trace=default; diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4913aac6c30..f7f5476ab23 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1203,8 +1203,8 @@ EXPLAIN SELECT DISTINCT a FROM t1 { }, { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "best_covering_index_scan": { @@ -1386,8 +1386,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a", "b", "c", "d"], - "usable": true + "usable": true, + "key_parts": ["a", "b", "c", "d"] } ], "best_covering_index_scan": { @@ -1585,8 +1585,8 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "potential_range_indexes": [ { "index": "id", - "key_parts": ["id", "a"], - "usable": true + "usable": true, + "key_parts": ["id", "a"] } ], "best_covering_index_scan": { @@ -1773,8 +1773,8 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "potential_range_indexes": [ { "index": "id", - "key_parts": ["id", "a"], - "usable": true + "usable": true, + "key_parts": ["id", "a"] } ], "best_covering_index_scan": { @@ -2012,13 +2012,13 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { }, { "index": "a_c", - "key_parts": ["a", "c"], - "usable": true + "usable": true, + "key_parts": ["a", "c"] }, { "index": "a_b", - "key_parts": ["a", "b"], - "usable": true + "usable": true, + "key_parts": ["a", "b"] } ], "setup_range_conditions": [], @@ -2215,8 +2215,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { }, { "index": "a_c", - "key_parts": ["a", "c"], - "usable": true + "usable": true, + "key_parts": ["a", "c"] }, { "index": "a_b", @@ -3231,18 +3231,18 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "potential_range_indexes": [ { "index": "pk", - "key_parts": ["pk"], - "usable": true + "usable": true, + "key_parts": ["pk"] }, { "index": "pk_a", - "key_parts": ["pk", "a"], - "usable": true + "usable": true, + "key_parts": ["pk", "a"] }, { "index": "pk_a_b", - "key_parts": ["pk", "a", "b"], - "usable": true + "usable": true, + "key_parts": ["pk", "a", "b"] } ], "best_covering_index_scan": { @@ -3749,8 +3749,8 @@ explain delete from t0 where t0.a<3 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "setup_range_conditions": [], @@ -3887,8 +3887,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "best_covering_index_scan": { @@ -3952,8 +3952,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] } ], "best_covering_index_scan": { diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 011875762d1..f1e13586eda 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -78,13 +78,13 @@ explain select * from t1 where a=1 or b=1 { "potential_range_indexes": [ { "index": "a", - "key_parts": ["a"], - "usable": true + "usable": true, + "key_parts": ["a"] }, { "index": "b", - "key_parts": ["b"], - "usable": true + "usable": true, + "key_parts": ["b"] }, { "index": "c", diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index d372be85bd8..0ddaaeae89d 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -93,13 +93,13 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "potential_range_indexes": [ { "index": "PRIMARY", - "key_parts": ["pk1", "pk2"], - "usable": true + "usable": true, + "key_parts": ["pk1", "pk2"] }, { "index": "key1", - "key_parts": ["key1"], - "usable": true + "usable": true, + "key_parts": ["key1"] }, { "index": "key2", diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 541a921435a..ae2b5060625 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2809,12 +2809,11 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, trace_idx_details.add("usable", false).add("cause", "fulltext"); continue; // ToDo: ft-keys in non-ft ranges, if possible SerG } - + trace_idx_details.add("usable", true); param.key[param.keys]=key_parts; key_part_info= key_info->key_part; uint cur_key_len= 0; Json_writer_array trace_keypart(thd, "key_parts"); - bool unusable_has_desc_keyparts= false; for (uint part= 0 ; part < n_key_parts ; part++, key_parts++, key_part_info++) { @@ -2829,18 +2828,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, (key_info->flags & HA_SPATIAL) ? Field::itMBR : Field::itRAW; /* Only HA_PART_KEY_SEG is used */ key_parts->flag= (uint8) key_part_info->key_part_flag; - if (key_part_info->key_part_flag & HA_REVERSE_SORT) - unusable_has_desc_keyparts= true; trace_keypart.add(key_parts->field->field_name); } trace_keypart.end(); - trace_idx_details.add("usable", !unusable_has_desc_keyparts); - unusable_has_desc_keyparts= false; - if (unusable_has_desc_keyparts) // TODO MDEV-13756 - { - key_parts= param.key[param.keys]; - continue; - } param.real_keynr[param.keys++]=idx; if (cur_key_len > max_key_len) max_key_len= cur_key_len; @@ -13833,6 +13823,17 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) cause= "not covering"; goto next_index; } + + { + for (uint i= 0; i < table->actual_n_key_parts(cur_index_info); i++) + { + if (cur_index_info->key_part[i].key_part_flag & HA_REVERSE_SORT) + { + cause="Reverse-ordered (not supported yet)"; + goto next_index; + } + } + } /* This function is called on the precondition that the index is covering.
participants (1)
-
psergey