#enable optimizer_record_context
set optimizer_record_context=ON;
set @saved_opt_context_var_name_1= 'saved_opt_context_1';
set @saved_opt_context_var_name_2= 'saved_opt_context_2';
set @opt_context_var_name= 'opt_context';
create database db1;
use db1;
create table t1
(
a int, b int,
index t1_idx_a (a),
index t1_idx_b (b),
index t1_idx_ab (a, b)
) ENGINE=MyISAM;
insert into t1 select seq%5, seq%8 from seq_1_to_20;
set session use_stat_tables='COMPLEMENTARY';
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status Table is already up to date
#
# simple query after analyzing the table
# planner should pick the analyzed table stats
#
select * from t1 where a < 3 and b > 6;
a b
0 7
2 7
# == Optimizer Context
# === Tables
# Tables in the context
table_name file_stat_records index_name rec_per_key
db1.t1 20 NULL NULL
# === Range accesses
index_name ranges num_rows max_index_blocks max_row_blocks
t1_idx_a ["(NULL) < (a) < (3)"] 12 1 1
t1_idx_b ["(6) < (b)"] 2 1 1
t1_idx_ab ["(NULL) < (a) < (3)"] 12 1 1
# == End of optimizer context
set @saved_opt_context_1=@opt_context;
set @saved_table_contexts= @table_contexts;
set @saved_list_ranges_1=@list_ranges;
#
# load stats in JSON format into variable optimizer_replay_context
# and rerun the query.
# These loaded stats are same as the analyzed stats
#
set optimizer_replay_context=@saved_opt_context_var_name_1;
select * from t1 where a < 3 and b > 6;
a b
0 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_1, @opt_context);
JSON_EQUALS(@saved_opt_context_1, @opt_context)
1
#
# set the variable optimizer_replay_context to blank data
# and rerun the query.
# Analyzed table stats should be used for query planning
#
set optimizer_replay_context="";
select * from t1 where a < 3 and b > 6;
a b
0 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_1, @opt_context);
JSON_EQUALS(@saved_opt_context_1, @opt_context)
1
#
# now update the table without running analyze table, and then rerun the queries
#
insert into t1 select seq%5, seq%8 from seq_1_to_20;
#
# Only range stats are different, as the table wasn't re-analyzed 
#
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_1, @opt_context);
JSON_EQUALS(@saved_opt_context_1, @opt_context)
0
set @list_ranges= (select JSON_DETAILED(
JSON_EXTRACT(@opt_context,
'$**.multi_range_read_info_const_calls')));
set @saved_list_ranges_2 = @list_ranges;
select JSON_EQUALS(@saved_list_ranges_2, @saved_list_ranges_1);
JSON_EQUALS(@saved_list_ranges_2, @saved_list_ranges_1)
0
select * from json_table(
@list_ranges,
'$[*][*]' columns(
index_name text path '$.index_name',
ranges json path '$.ranges',
num_rows int path '$.num_rows',
max_index_blocks int path '$.max_index_blocks',
max_row_blocks int path '$.max_row_blocks'
    )
) as jt;
index_name ranges num_rows max_index_blocks max_row_blocks
t1_idx_a ["(NULL) < (a) < (3)"] 24 1 1
t1_idx_b ["(6) < (b)"] 4 1 1
t1_idx_ab ["(NULL) < (a) < (3)"] 24 1 1
#
# Now, load stats in JSON format into variable optimizer_replay_context
# and rerun the query.
# Loaded stats (including range stats) should be picked by the planner
#
set optimizer_replay_context=@saved_opt_context_var_name_1;
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_1, @opt_context);
JSON_EQUALS(@saved_opt_context_1, @opt_context)
1
#
# with optimizer_record_context OFF,
# nothing gets printed to the trace
#
set optimizer_record_context=OFF;
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select @opt_context;
@opt_context
NULL
#
# Now, after re-enabling optimizer_record_context,
# Loaded stats should be picked by the planner instead of the analyzed table stats,
# as the optimizer_replay_context is still set to a valid json containing other stats
#
set optimizer_record_context=ON;
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_1, @opt_context);
JSON_EQUALS(@saved_opt_context_1, @opt_context)
1
#
# Now, set the variable optimizer_replay_context to blank data
# and rerun the query.
# Analyzed stats with updated range stats should be picked by the planner
#
set optimizer_replay_context="";
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_1, @opt_context);
JSON_EQUALS(@saved_opt_context_1, @opt_context)
0
set @list_ranges= (select JSON_DETAILED(
JSON_EXTRACT(@opt_context,
'$**.multi_range_read_info_const_calls')));
select JSON_EQUALS(@saved_list_ranges_2, @list_ranges);
JSON_EQUALS(@saved_list_ranges_2, @list_ranges)
1
#
# now re-analyze the table, and then rerun the queries
#
analyze table t1 persistent for all;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
#
# All the stats are updated as the table is re-analyzed 
#
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @list_ranges= (select JSON_DETAILED(JSON_EXTRACT(@opt_context, '$**.multi_range_read_info_const_calls')));
select JSON_EQUALS(@saved_list_ranges_2, @list_ranges);
JSON_EQUALS(@saved_list_ranges_2, @list_ranges)
1
set @saved_opt_context_2 = @opt_context;
#
# Now, load stats in JSON format into variable optimizer_replay_context
# and rerun the query.
# Loaded stats (including range stats) should be picked by the planner
#
set optimizer_replay_context=@saved_opt_context_var_name_1;
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_1, @opt_context);
JSON_EQUALS(@saved_opt_context_1, @opt_context)
1
#
# Now, set the variable optimizer_replay_context to blank data
# and rerun the query.
# All the re-analyzed stats should be picked by the planner
#
set optimizer_replay_context="";
select * from t1 where a < 3 and b > 6;
a b
0 7
0 7
2 7
2 7
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
select JSON_EQUALS(@saved_opt_context_2, @opt_context);
JSON_EQUALS(@saved_opt_context_2, @opt_context)
1
#
# The following tests check how Optimzer Context Parser reacts 
# to JSON elements missing in the optimizer_replay_context variable
#
set optimizer_replay_context=@opt_context_var_name;
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].name');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "name" element not present at offset 1575.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].ddl');
select * from t1 where a > 10;
a b
Warnings:
Warning 4270 Failed to match the stats from replay context with the optimizer stats: the given list of ranges i.e. [(10) < (a), ] doesn't exist in the list of ranges for table_name db1.t1 and index_name t1_idx_a
Warning 4270 Failed to match the stats from replay context with the optimizer stats: the given list of ranges i.e. [(10) < (a), ] doesn't exist in the list of ranges for table_name db1.t1 and index_name t1_idx_ab
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].file_stat_records');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "file_stat_records" element not present at offset 1568.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].indexes[0].index_name');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "index_name" element not present at offset 209.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].indexes[0].rec_per_key');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "rec_per_key" element not present at offset 215.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0].index_name');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "index_name" element not present at offset 736.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0].ranges');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "ranges" element not present at offset 728.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0].num_rows');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "num_rows" element not present at offset 746.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0].cost');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "cost" element not present at offset 530.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0].max_index_blocks');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "max_index_blocks" element not present at offset 739.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0].max_row_blocks');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "max_row_blocks" element not present at offset 741.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0].call_number');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "call_number" element not present at offset 744.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].indexes[0]');
select * from t1 where a > 10;
a b
Warnings:
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1.t1_idx_a doesn't exist in list of index contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: the given list of ranges i.e. [(10) < (a), ] doesn't exist in the list of ranges for table_name db1.t1 and index_name t1_idx_a
Warning 4270 Failed to match the stats from replay context with the optimizer stats: the given list of ranges i.e. [(10) < (a), ] doesn't exist in the list of ranges for table_name db1.t1 and index_name t1_idx_ab
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].multi_range_read_info_const_calls[0]');
select * from t1 where a > 10;
a b
Warnings:
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1.t1_idx_a doesn't exist in list of range contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: the given list of ranges i.e. [(10) < (a), ] doesn't exist in the list of ranges for table_name db1.t1 and index_name t1_idx_ab
set @opt_context=json_replace(@saved_opt_context_1, '$.tables[0].name', 'db2.t1');
select * from t1 where a > 10;
a b
Warnings:
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1 doesn't exist in list of table contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1.t1_idx_a doesn't exist in list of index contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1.t1_idx_b doesn't exist in list of index contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1.t1_idx_ab doesn't exist in list of index contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1 doesn't exist in list of table contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1.t1_idx_a doesn't exist in list of range contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1.t1_idx_ab doesn't exist in list of range contexts
Warning 4270 Failed to match the stats from replay context with the optimizer stats: db1.t1 doesn't exist in list of table contexts
set optimizer_replay_context="";
explain select tt1.a, tt2.b from t1 tt1, t1 tt2 where tt1.a = tt2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tt1 index t1_idx_a,t1_idx_ab t1_idx_a 5 NULL 40 Using where; Using index
1 SIMPLE tt2 ref t1_idx_a,t1_idx_ab t1_idx_ab 5 db1.tt1.a 8 Using index
set @opt_context=
(select REGEXP_SUBSTR(
context,
'(?<=set @opt_context=\')([\n\r].*)*(?=\'\;#opt_context_ends)')
from information_schema.optimizer_context);
set @saved_opt_context_1= @opt_context;
set optimizer_replay_context=@opt_context_var_name;
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].index_name');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "index_name" element not present at offset 613.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].num_records');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "num_records" element not present at offset 621.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].eq_ref');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "eq_ref" element not present at offset 626.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].index_cost_io');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "index_cost_io" element not present at offset 619.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].index_cost_cpu');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "index_cost_cpu" element not present at offset 608.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].row_cost_io');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "row_cost_io" element not present at offset 621.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].row_cost_cpu');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "row_cost_cpu" element not present at offset 610.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].max_index_blocks');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "max_index_blocks" element not present at offset 616.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].max_row_blocks');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "max_row_blocks" element not present at offset 618.
set @opt_context=json_remove(@saved_opt_context_1, '$.tables[0].cost_for_index_read_calls[0].copy_cost');
select * from t1 where a > 10;
a b
Warnings:
Warning 4269 Failed to parse saved optimizer context: "copy_cost" element not present at offset 623.
drop table t1;
drop database db1;