Hi Varun,
In general, the patch moves in the right direction, but it is not yet there.
Consider this example:
create table ten(a int);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (
a int not null,
b int not null,
c int not null,
d int not null,
key(a,b,c)
);
insert into t1 select a,a, a,a from ten;
set optimizer_trace=1;
explain select * from t1 where a between 1 and 4 and b < 50;
mysql> select
-> JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives'))
-> from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
{
"range_scan_alternatives":
[
{
"index": "a",
"ranges":
[
"(a) < (4,50)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.5002,
"chosen": true
}
],
"analyzing_roworder_intersect":
{
"cause": "too few roworder scans"
},
"analyzing_index_merge_union":
[
]
}
] |
while print_quick produces:
T@18 : | | | | | | | | | | >print_quick
quick range select, key a, length: 8
1 <= X < 4/50
other_keys: 0x0:
T@18 : | | | | | | | | | | <print_quick
Please investigate why this happens.
On Wed, Apr 03, 2019 at 01:16:56PM +0530, Varun wrote:
Any idea why does the commit trigger show "mariadb-10.3.." ? The patch is
against 10.4, isnt it?
...
/*
@@ -2273,10 +2275,7 @@ void TRP_RANGE::trace_basic_info(const PARAM *param,
// TRP_RANGE should not be created if there are no range intervals
DBUG_ASSERT(key);
- String range_info;
- range_info.length(0);
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info, key, key_part);
+ append_range_all_keyparts(&trace_range, *param, key_idx, key, key_part);
}
@@ -2489,10 +2488,8 @@ void TRP_GROUP_MIN_MAX::trace_basic_info(const PARAM *param,
// can have group quick without ranges
if (index_tree)
{
- String range_info;
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info, index_tree,
- key_part);
+ append_range_all_keyparts(&trace_range, *param, param_idx,
+ index_tree, key_part);
}
}
@@ -6398,20 +6395,9 @@ void TRP_ROR_INTERSECT::trace_basic_info(const PARAM *param,
trace_isect_idx.add("rows", (*cur_scan)->records);
Json_writer_array trace_range(thd, "ranges");
- for (const SEL_ARG *current= (*cur_scan)->sel_arg->first(); current;
- current= current->next)
- {
- String range_info;
- range_info.set_charset(system_charset_info);
- for (const SEL_ARG *part= current; part;
- part= part->next_key_part ? part->next_key_part : nullptr)
- {
- const KEY_PART_INFO *cur_key_part= key_part + part->part;
- append_range(&range_info, cur_key_part, part->min_value,
- part->max_value, part->min_flag | part->max_flag);
- }
- trace_range.add(range_info.ptr(), range_info.length());
- }
+
+ append_range_all_keyparts(&trace_range, *param, (*cur_scan)->idx,
+ (*cur_scan)->sel_arg->first(), key_part);
}
}
@@ -7389,9 +7375,6 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
const KEY &cur_key= param->table->key_info[keynr];
const KEY_PART_INFO *key_part= cur_key.key_part;
- String range_info;
- range_info.set_charset(system_charset_info);
-
index_scan->idx= idx;
index_scan->keynr= keynr;
index_scan->key_info= ¶m->table->key_info[keynr];
@@ -7402,8 +7385,8 @@ static TRP_RANGE *get_key_scans_params(PARAM *param, SEL_TREE *tree,
*tree->index_scans_end++= index_scan;
if (unlikely(thd->trace_started()))
- append_range_all_keyparts(&trace_range, NULL, &range_info, key,
- key_part);
+ append_range_all_keyparts(&trace_range, (*param), idx,
+ key, key_part);
Please fix indentation ^
trace_range.end();
trace_idx.add("rowid_ordered", param->is_ror_scan)
@@ -13554,11 +13537,8 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time)
Json_writer_array trace_range(thd, "ranges");
const KEY_PART_INFO *key_part= cur_index_info->key_part;
-
- String range_info;
- range_info.set_charset(system_charset_info);
- append_range_all_keyparts(&trace_range, NULL, &range_info,
- cur_index_tree, key_part);
+ append_range_all_keyparts(&trace_range, *param, cur_param_idx,
+ cur_index_tree, key_part);
Please fix indentation ^
}
}
cost_group_min_max(table, cur_index_info, cur_used_key_parts,
@@ -15730,12 +15710,17 @@ void QUICK_GROUP_MIN_MAX_SELECT::dbug_dump(int indent, bool verbose)
}
#endif /* !DBUG_OFF */
+
static
void append_range(String *out, const KEY_PART_INFO *key_part,
- const uchar *min_key, const uchar *max_key, const uint flag)
+ KEY_MULTI_RANGE *range, uint n_key_parts)
{
- if (out->length() > 0)
- out->append(STRING_WITH_LEN(" AND "));
+ uint flag= range->range_flag;
+ String key_name;
+ key_name.set_charset(system_charset_info);
+ key_part_map keypart_map= range->start_key.length ?
+ range->start_key.keypart_map :
+ range->end_key.keypart_map;
if (flag & GEOM_FLAG)
{
@@ -15744,22 +15729,24 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
range types, so printing "col < some_geom" doesn't make sense.
Just print the column name, not operator.
*/
- out->append(key_part->field->field_name);
+ print_keyparts_name(out, key_part, n_key_parts, keypart_map);
out->append(STRING_WITH_LEN(" "));
- print_key_value(out, key_part, min_key);
+ print_key_value(out, key_part, range->start_key.key,
+ range->start_key.length);
return;
}
if (!(flag & NO_MIN_RANGE))
{
- print_key_value(out, key_part, min_key);
+ print_key_value(out, key_part, range->start_key.key,
+ range->start_key.length);
if (flag & NEAR_MIN)
out->append(STRING_WITH_LEN(" < "));
else
out->append(STRING_WITH_LEN(" <= "));
}
- out->append(key_part->field->field_name);
+ print_keyparts_name(out, key_part, n_key_parts, keypart_map);
if (!(flag & NO_MAX_RANGE))
{
@@ -15767,7 +15754,8 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
out->append(STRING_WITH_LEN(" < "));
else
out->append(STRING_WITH_LEN(" <= "));
- print_key_value(out, key_part, max_key);
+ print_key_value(out, key_part, range->end_key.key,
+ range->end_key.length);
}
}
@@ -15775,60 +15763,39 @@ void append_range(String *out, const KEY_PART_INFO *key_part,
Add ranges to the trace
For ex:
- query: select * from t1 where a=2 ;
- and we have an index on a , so we create a range
- 2 <= a <= 2
+ lets say we have an index a_b(a,b)
+ query: select * from t1 where a=2 and b=4 ;
+ so we create a range:
+ (2,4) <= (a,b) <= (2,4)
this is added to the trace
*/
static void append_range_all_keyparts(Json_writer_array *range_trace,
- String *range_string,
- String *range_so_far, const SEL_ARG *keypart,
+ PARAM param, uint idx,
+ SEL_ARG *keypart,
const KEY_PART_INFO *key_parts)
{
-
- DBUG_ASSERT(keypart);
- DBUG_ASSERT(keypart && keypart != &null_element);
-
- // Navigate to first interval in red-black tree
+ SEL_ARG_RANGE_SEQ seq;
+ KEY_MULTI_RANGE range;
+ range_seq_t seq_it;
+ uint flags= 0;
+ RANGE_SEQ_IF seq_if = {NULL, sel_arg_range_seq_init,
+ sel_arg_range_seq_next, 0, 0};
+ KEY *keyinfo= param.table->key_info + param.real_keynr[idx];
+ uint n_key_parts= param.table->actual_n_key_parts(keyinfo);
+ seq.keyno= idx;
+ seq.real_keyno= param.real_keynr[idx];
+ seq.param= ¶m;
+ seq.start= keypart;
const KEY_PART_INFO *cur_key_part= key_parts + keypart->part;
- const SEL_ARG *keypart_range= keypart->first();
- const size_t save_range_so_far_length= range_so_far->length();
-
+ seq_it= seq_if.init((void *) &seq, 0, flags);
- while (keypart_range)
+ while (!seq_if.next(seq_it, &range))
{
- // Append the current range predicate to the range String
- switch (keypart->type)
- {
- case SEL_ARG::Type::KEY_RANGE:
- append_range(range_so_far, cur_key_part, keypart_range->min_value,
- keypart_range->max_value,
- keypart_range->min_flag | keypart_range->max_flag);
- break;
- case SEL_ARG::Type::MAYBE_KEY:
- range_so_far->append("MAYBE_KEY");
- break;
- case SEL_ARG::Type::IMPOSSIBLE:
- range_so_far->append("IMPOSSIBLE");
- break;
- default:
- DBUG_ASSERT(false);
- break;
- }
-
- if (keypart_range->next_key_part &&
- keypart_range->next_key_part->part ==
- keypart_range->part + 1 &&
- keypart_range->is_singlepoint())
- {
- append_range_all_keyparts(range_trace, range_string, range_so_far,
- keypart_range->next_key_part, key_parts);
- }
- else
- range_trace->add(range_so_far->c_ptr_safe(), range_so_far->length());
- keypart_range= keypart_range->next;
- range_so_far->length(save_range_so_far_length);
+ String range_info;
+ range_info.set_charset(system_charset_info);
+ append_range(&range_info, cur_key_part, &range, n_key_parts);
+ range_trace->add(range_info.c_ptr_safe(), range_info.length());
}
}
@@ -15838,70 +15805,110 @@ static void append_range_all_keyparts(Json_writer_array *range_trace,
@param[out] out String the key is appended to
@param[in] key_part Index components description
@param[in] key Key tuple
+ @param[in] used_length length of the key tuple
*/
+
static void print_key_value(String *out, const KEY_PART_INFO *key_part,
- const uchar *key)
+ const uchar* key, uint used_length)
{
+ out->append(STRING_WITH_LEN("("));
Field *field= key_part->field;
+ StringBuffer<128> tmp(system_charset_info);
+ TABLE *table= field->table;
+ uint store_length;
+ my_bitmap_map *old_sets[2];
+ dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
+ const uchar *key_end= key+used_length;
- if (field->flags & BLOB_FLAG)
+ for (; key < key_end; key+=store_length, key_part++)
{
- // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
- if (field->real_maybe_null() && *key)
- out->append(STRING_WITH_LEN("NULL"));
- else
- (field->type() == MYSQL_TYPE_GEOMETRY)
- ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
- : out->append(STRING_WITH_LEN("unprintable_blob_value"));
- return;
- }
+ field= key_part->field;
+ store_length= key_part->store_length;
+ if (field->flags & BLOB_FLAG)
+ {
+ // Byte 0 of a nullable key is the null-byte. If set, key is NULL.
+ if (field->real_maybe_null() && *key)
+ out->append(STRING_WITH_LEN("NULL"));
+ else
+ (field->type() == MYSQL_TYPE_GEOMETRY)
+ ? out->append(STRING_WITH_LEN("unprintable_geometry_value"))
+ : out->append(STRING_WITH_LEN("unprintable_blob_value"));
+ goto next;
+ }
- uint store_length= key_part->store_length;
+ if (field->real_maybe_null())
+ {
+ /*
+ Byte 0 of key is the null-byte. If set, key is NULL.
+ Otherwise, print the key value starting immediately after the
+ null-byte
+ */
+ if (*key)
+ {
+ out->append(STRING_WITH_LEN("NULL"));
+ goto next;
+ }
+ key++; // Skip null byte
+ store_length--;
+ }
- if (field->real_maybe_null())
- {
/*
- Byte 0 of key is the null-byte. If set, key is NULL.
- Otherwise, print the key value starting immediately after the
- null-byte
+ Binary data cannot be converted to UTF8 which is what the
+ optimizer trace expects. If the column is binary, the hex
+ representation is printed to the trace instead.
*/
- if (*key)
+ if (field->flags & BINARY_FLAG)
{
- out->append(STRING_WITH_LEN("NULL"));
- return;
+ out->append("0x");
+ for (uint i = 0; i < store_length; i++)
+ {
+ out->append(_dig_vec_lower[*(key + i) >> 4]);
+ out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+ }
+ goto next;
}
- key++; // Skip null byte
- store_length--;
+
+ field->set_key_image(key, key_part->length);
+ if (field->type() == MYSQL_TYPE_BIT)
+ (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
+ else
+ field->val_str(&tmp); // may change tmp's charset
+ out->append(tmp.ptr(), tmp.length(), tmp.charset());
+
+ next:
+ if (key + store_length < key_end)
+ out->append(STRING_WITH_LEN(","));
}
+ dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
+ out->append(STRING_WITH_LEN(")"));
+}
- /*
- Binary data cannot be converted to UTF8 which is what the
- optimizer trace expects. If the column is binary, the hex
- representation is printed to the trace instead.
- */
- if (field->flags & BINARY_FLAG)
+/**
+ Print key parts involed in a range
+ @param[out] out String the key is appended to
+ @param[in] key_part Index components description
+ @param[in] n_keypart Number of keyparts in index
+ @param[in] keypart_map map for keyparts involved in the range
+*/
+
+void print_keyparts_name(String *out, const KEY_PART_INFO *key_part,
+ uint n_keypart, key_part_map keypart_map)
+{
+ uint i;
+ out->append(STRING_WITH_LEN("("));
+ bool first_keypart= TRUE;
+ for (i=0; i < n_keypart; key_part++, i++)
{
- out->append("0x");
- for (uint i = 0; i < store_length; i++)
+ if (keypart_map & (1 << i))
{
- out->append(_dig_vec_lower[*(key + i) >> 4]);
- out->append(_dig_vec_lower[*(key + i) & 0x0F]);
+ if (first_keypart)
+ first_keypart= FALSE;
+ else
+ out->append(STRING_WITH_LEN(","));
+ out->append(key_part->field->field_name);
}
- return;
+ else
+ break;
}
-
- StringBuffer<128> tmp(system_charset_info);
- TABLE *table= field->table;
- my_bitmap_map *old_sets[2];
-
- dbug_tmp_use_all_columns(table, old_sets, table->read_set, table->write_set);
-
- field->set_key_image(key, key_part->length);
- if (field->type() == MYSQL_TYPE_BIT)
- (void)field->val_int_as_str(&tmp, 1); // may change tmp's charset
- else
- field->val_str(&tmp); // may change tmp's charset
- out->append(tmp.ptr(), tmp.length(), tmp.charset());
-
- dbug_tmp_restore_column_maps(table->read_set, table->write_set, old_sets);
-}
+ out->append(STRING_WITH_LEN(")"));
+}
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
Sergey Petrunia