diff options
author | Monty <monty@mariadb.org> | 2023-03-02 15:48:28 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2023-03-03 13:25:21 +0200 |
commit | 922fcc6a0e3fe4fee4e1e4942b11bd25fadac97a (patch) | |
tree | eb7ce2d4a215b2ad63c10ed90189c8fffae092e1 | |
parent | ae0509771462c02149e294f8f8593d6ff10755f7 (diff) | |
download | mariadb-git-922fcc6a0e3fe4fee4e1e4942b11bd25fadac97a.tar.gz |
Use range instead of ref when we know that range is equal or better.
This stabilizes main.order_by_optimizer_innodb, where the result varies
depending on the rec_per_key status from the engine.
The logic to prefer range over a const ref:
- If range of has only one part and it uses more key parts than ref, then
use the range.
Example:
WHERE key_part1=1 and key_part2 > #
Here we will prefer a range over (key_part1,key_part2) instead a ref
over key_part1.
-rw-r--r-- | sql/sql_select.cc | 32 |
1 files changed, 25 insertions, 7 deletions
diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d4e399975f6..78d59493f97 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -8567,7 +8567,8 @@ best_access_path(JOIN *join, */ if (table->opt_range_keys.is_set(key) && !found_ref && //(C1) table->opt_range[key].key_parts == max_key_part && //(C2) - table->opt_range[key].ranges == 1 + MY_TEST(ref_or_null_part)) //(C3) + (table->opt_range[key].ranges == + 1 + MY_TEST(ref_or_null_part))) //(C3) { records= (double) table->opt_range[key].rows; table->opt_range[key].get_costs(&tmp); @@ -8601,17 +8602,34 @@ best_access_path(JOIN *join, */ if (table->opt_range_keys.is_set(key)) { + double rows; if (table->opt_range[key].key_parts >= max_key_part) // (2) { - double rows= (double) table->opt_range[key].rows; - if (!found_ref && // (1) - records < rows) // (3) + /* + Choose range over REF in the case range will always be + as good or better than REF. + This is the case when we have only one const range + and it consist of more parts than what we used for REF. + */ + if (!found_ref && + table->opt_range[key].key_parts > max_key_part && + table->opt_range[key].ranges <= + (uint) (1 + MY_TEST(ref_or_null_part))) { - trace_access_idx.add("used_range_estimates", - "clipped up"); - records= rows; + trace_access_idx. + add("chosen", false). + add("cause", "range is simple and more selective"); + continue; // continue with next key } } + rows= (double) table->opt_range[key].rows; + if (!found_ref && // (1) + records < rows) // (3) + { + trace_access_idx.add("used_range_estimates", + "clipped up"); + records= rows; + } } } else |