Quelle  opt_context_replay_innodb_pref.result   Sprache: Lisp

set @opt_context_schema='$opt_context_schema';
set session use_stat_tables='PREFERABLY';
set optimizer_record_context=OFF;
create database db1;
use db1;
create function round_cost(explain_output text)
returns text
begin
declare len int default 0;
declare prec int default 7;
declare cost_elem text;
declare cost_value_json text;
declare cost_value double;
set cost_elem= '$.query_block[0].cost';
set cost_value_json= (select json_extract(explain_output, cost_elem));
set explain_output= (select json_replace(explain_output, cost_elem, round(cost_value_json, prec)));
set len= (select json_length(json_extract(explain_output, '$.query_block.nested_loop')));
for i in 0 .. len-1
do
set cost_elem= concat('$.query_block[0].nested_loop[', i, '].table.cost');
set cost_value_json= (select json_extract(explain_output, cost_elem));
set explain_output= (select json_replace(explain_output, cost_elem, round(cost_value_json, prec)));
set cost_elem= concat('$.query_block[0].nested_loop[', i, '].block-nl-join.table.cost');
set cost_value_json= (select json_extract(explain_output, cost_elem));
set explain_output= (select json_replace(explain_output, cost_elem, round(cost_value_json, prec)));
end for;
return explain_output;
end;
//
#
# Range query on a single table by using 1 unique index on a single column
#
create table t1 (
c1 int,
c2 int,
unique(c1),
unique(c2)
) Engine=InnoDB;
insert into t1 select seq, seq from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_record_context=ON;
select count(*) from t1;
count(*)
100
set @set_stmts=
(select REGEXP_SUBSTR(context, '(SET .*)([\n\r].*)*(?=(CREATE DATABASE))')
AS set_stmt from information_schema.optimizer_context);
select @set_stmts;
@set_stmts
SET NAMES utf8mb4;

SET GLOBAL InnoDB.OPTIMIZER_DISK_READ_COST=10.24;
SET GLOBAL InnoDB.OPTIMIZER_INDEX_BLOCK_COPY_COST=0.0356;
SET GLOBAL InnoDB.OPTIMIZER_KEY_COMPARE_COST=0.011361;
SET GLOBAL InnoDB.OPTIMIZER_KEY_COPY_COST=0.015685;
SET GLOBAL InnoDB.OPTIMIZER_KEY_LOOKUP_COST=0.79112;
SET GLOBAL InnoDB.OPTIMIZER_KEY_NEXT_FIND_COST=0.099;
SET GLOBAL InnoDB.OPTIMIZER_DISK_READ_RATIO=0.02;
SET GLOBAL InnoDB.OPTIMIZER_ROW_COPY_COST=0.06087;
SET GLOBAL InnoDB.OPTIMIZER_ROW_LOOKUP_COST=0.76597;
SET GLOBAL InnoDB.OPTIMIZER_ROW_NEXT_FIND_COST=0.07013;
SET GLOBAL InnoDB.OPTIMIZER_ROWID_COMPARE_COST=0.002653;
SET GLOBAL InnoDB.OPTIMIZER_ROWID_COPY_COST=0.002653;
SET GLOBAL heap.OPTIMIZER_DISK_READ_COST=0;
SET GLOBAL heap.OPTIMIZER_INDEX_BLOCK_COPY_COST=0;
SET GLOBAL heap.OPTIMIZER_KEY_COMPARE_COST=0.011361;
SET GLOBAL heap.OPTIMIZER_KEY_COPY_COST=0;
SET GLOBAL heap.OPTIMIZER_KEY_LOOKUP_COST=0;
SET GLOBAL heap.OPTIMIZER_KEY_NEXT_FIND_COST=0;
SET GLOBAL heap.OPTIMIZER_DISK_READ_RATIO=0;
SET GLOBAL heap.OPTIMIZER_ROW_COPY_COST=0.002334;
SET GLOBAL heap.OPTIMIZER_ROW_LOOKUP_COST=0;
SET GLOBAL heap.OPTIMIZER_ROW_NEXT_FIND_COST=0.0080166;
SET GLOBAL heap.OPTIMIZER_ROWID_COMPARE_COST=0.002653;
SET GLOBAL heap.OPTIMIZER_ROWID_COPY_COST=0.002653;
SET GLOBAL temp_table.OPTIMIZER_DISK_READ_COST=10.24;
SET GLOBAL temp_table.OPTIMIZER_INDEX_BLOCK_COPY_COST=0.0356;
SET GLOBAL temp_table.OPTIMIZER_KEY_COMPARE_COST=0.011361;
SET GLOBAL temp_table.OPTIMIZER_KEY_COPY_COST=0.015685;
SET GLOBAL temp_table.OPTIMIZER_KEY_LOOKUP_COST=0.435777;
SET GLOBAL temp_table.OPTIMIZER_KEY_NEXT_FIND_COST=0.082347;
SET GLOBAL temp_table.OPTIMIZER_DISK_READ_RATIO=0.02;
SET GLOBAL temp_table.OPTIMIZER_ROW_COPY_COST=0.060866;
SET GLOBAL temp_table.OPTIMIZER_ROW_LOOKUP_COST=0.130839;
SET GLOBAL temp_table.OPTIMIZER_ROW_NEXT_FIND_COST=0.045916;
SET GLOBAL temp_table.OPTIMIZER_ROWID_COMPARE_COST=0.002653;
SET GLOBAL temp_table.OPTIMIZER_ROWID_COPY_COST=0.002653;
SET group_concat_max_len=1048576;
SET in_predicate_conversion_threshold=1000;
SET innodb_strict_mode='ON';
SET join_buffer_size=262144;
SET join_cache_level=2;
SET max_heap_table_size=1048576;
SET note_verbosity='basic,explain';
SET old_mode='';
SET optimizer_adjust_secondary_key_costs=0;
SET GLOBAL optimizer_disk_read_cost=10.240000;
SET GLOBAL optimizer_disk_read_ratio=0.020000;
SET optimizer_extra_pruning_depth=8;
SET GLOBAL optimizer_index_block_copy_cost=0.035600;
SET optimizer_join_limit_pref_ratio=0;
SET GLOBAL optimizer_key_compare_cost=0.011361;
SET GLOBAL optimizer_key_copy_cost=0.015685;
SET GLOBAL optimizer_key_lookup_cost=0.435777;
SET GLOBAL optimizer_key_next_find_cost=0.082347;
SET optimizer_max_sel_arg_weight=32000;
SET optimizer_max_sel_args=16000;
SET optimizer_prune_level=2;
SET GLOBAL optimizer_row_copy_cost=0.060866;
SET GLOBAL optimizer_row_lookup_cost=0.130839;
SET GLOBAL optimizer_row_next_find_cost=0.045916;
SET GLOBAL optimizer_rowid_compare_cost=0.002653;
SET GLOBAL optimizer_rowid_copy_cost=0.002653;
SET optimizer_scan_setup_cost=10.000000;
SET optimizer_search_depth=62;
SET optimizer_selectivity_sampling_limit=100;
SET optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=on,cset_narrowing=on,sargable_casefold=on,reorder_outer_joins=off';
SET optimizer_trace='enabled=off';
SET optimizer_trace_max_mem_size=1048576;
SET optimizer_use_condition_selectivity=4;
SET optimizer_where_cost=0.032000;
SET sort_buffer_size=262144;
SET sql_buffer_result='OFF';
SET sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET standard_compliant_cte='ON';
SET time_zone='REPLACED';
SET timestamp=REPLACED;
## version='REPLACED';
## version_source_revision='REPLACED';

set optimizer_record_context=OFF;
set optimizer_replay_context= "";
explain format= json select * from t1 a,
t1 b where a.c1 < 3 and b.c1 < 33
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0384631,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "a",
                    "access_type": "range",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "loops": 1,
                    "rows": 2,
                    "cost": 0.0052431,
                    "filtered": 100,
                    "index_condition": "a.c1 < 3"
                }
            },
            {
                "block-nl-join": 
                {
                    "table": 
                    {
                        "table_name": "b",
                        "access_type": "ALL",
                        "possible_keys": 
                        ["c1"],
                        "loops": 2,
                        "rows": 100,
                        "cost": 0.0332200,
                        "filtered": 32,
                        "attached_condition": "b.c1 < 33"
                    },
                    "buffer_type": "flat",
                    "buffer_size": "119",
                    "join_type": "BNL"
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.038463076,
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "range",
          "possible_keys": ["c1"],
          "key": "c1",
          "key_length": "5",
          "used_key_parts": ["c1"],
          "loops": 1,
          "rows": 2,
          "cost": 0.00524312,
          "filtered": 100,
          "index_condition": "a.c1 < 3"
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "b",
            "access_type": "ALL",
            "possible_keys": ["c1"],
            "loops": 2,
            "rows": 100,
            "cost": 0.033219956,
            "filtered": 32,
            "attached_condition": "b.c1 < 33"
          },
          "buffer_type": "flat",
          "buffer_size": "119",
          "join_type": "BNL"
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Equi-Join query on a single table having 1 non-unique index on a single column.
# Also, index column is used in the condition
#
create table t1 (
c1 int,
c2 int,
index(c1)
) Engine=InnoDB;
insert into t1 select seq%5, seq%10 from seq_1_to_200;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 3.8137228,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["c1"],
                    "loops": 1,
                    "rows": 200,
                    "cost": 0.0434548,
                    "filtered": 100
                }
            },
            {
                "block-nl-join": 
                {
                    "table": 
                    {
                        "table_name": "tt2",
                        "access_type": "ALL",
                        "possible_keys": 
                        ["c1"],
                        "loops": 200,
                        "rows": 200,
                        "cost": 3.7702680,
                        "filtered": 20
                    },
                    "buffer_type": "flat",
                    "buffer_size": "2KiB",
                    "join_type": "BNL",
                    "attached_condition": "tt2.c1 = tt1.c1"
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 3.8137228,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ALL",
          "possible_keys": ["c1"],
          "loops": 1,
          "rows": 200,
          "cost": 0.0434548,
          "filtered": 100
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "tt2",
            "access_type": "ALL",
            "possible_keys": ["c1"],
            "loops": 200,
            "rows": 200,
            "cost": 3.770268,
            "filtered": 20
          },
          "buffer_type": "flat",
          "buffer_size": "2KiB",
          "join_type": "BNL",
          "attached_condition": "tt2.c1 = tt1.c1"
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Equi-Join query on a single table having 1 primary key index on a single column.
# Also, index column is used in the condition
#
create table t1 (
c1 int,
c2 int,
primary key (c1)
) Engine=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.1174180,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["PRIMARY"],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "eq_ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["db1.tt1.c1"],
                    "loops": 100,
                    "rows": 1,
                    "cost": 0.0902632,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.117418,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 100,
          "cost": 0.0271548,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "tt2",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["c1"],
          "ref": ["db1.tt1.c1"],
          "loops": 100,
          "rows": 1,
          "cost": 0.0902632,
          "filtered": 100
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Equi-Join query on a single table having 1 primary key index on 2 columns.
# Both the index columns are used in the condition
#
create table t1 (
c1 int,
c2 int,
primary key (c1, c2)
) Engine=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1 and tt1.c2 = tt2.c2
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.1174180,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["PRIMARY"],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "eq_ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "8",
                    "used_key_parts": 
                    [
                        "c1",
                        "c2"
                    ],
                    "ref": 
                    [
                        "db1.tt1.c1",
                        "db1.tt1.c2"
                    ],
                    "loops": 100,
                    "rows": 1,
                    "cost": 0.0902632,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.117418,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 100,
          "cost": 0.0271548,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "tt2",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "8",
          "used_key_parts": ["c1", "c2"],
          "ref": ["db1.tt1.c1", "db1.tt1.c2"],
          "loops": 100,
          "rows": 1,
          "cost": 0.0902632,
          "filtered": 100
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Equi-Join query on a single table having 1 non-unique index on 2 columns.
# However, only 1 column from the index is used in the condition
#
create table t1 (
c1 int,
c2 int,
index(c1, c2)
) Engine=InnoDB;
insert into t1 select seq%5, seq%10 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.3981756,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "index",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "10",
                    "used_key_parts": 
                    [
                        "c1",
                        "c2"
                    ],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0213144,
                    "filtered": 100,
                    "attached_condition": "tt1.c1 is not null",
                    "using_index": true
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "ref",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["db1.tt1.c1"],
                    "loops": 100,
                    "rows": 20,
                    "cost": 0.3768612,
                    "filtered": 100,
                    "using_index": true
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.39817562,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "index",
          "possible_keys": ["c1"],
          "key": "c1",
          "key_length": "10",
          "used_key_parts": ["c1", "c2"],
          "loops": 1,
          "rows": 100,
          "cost": 0.02131442,
          "filtered": 100,
          "attached_condition": "tt1.c1 is not null",
          "using_index": true
        }
      },
      {
        "table": {
          "table_name": "tt2",
          "access_type": "ref",
          "possible_keys": ["c1"],
          "key": "c1",
          "key_length": "5",
          "used_key_parts": ["c1"],
          "ref": ["db1.tt1.c1"],
          "loops": 100,
          "rows": 20,
          "cost": 0.3768612,
          "filtered": 100,
          "using_index": true
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Equi-Join query on a single table having 1 primary key index on 2 columns.
# However, only 1 column from the index that has unique values is used in the condition
#
create table t1 (
c1 int,
c2 int,
primary key (c1, c2)
) Engine=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c1 = tt2.c1
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.1244310,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "possible_keys": 
                    ["PRIMARY"],
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "table": 
                {
                    "table_name": "tt2",
                    "access_type": "ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["db1.tt1.c1"],
                    "loops": 100,
                    "rows": 1,
                    "cost": 0.0972762,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.124431,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY"],
          "loops": 1,
          "rows": 100,
          "cost": 0.0271548,
          "filtered": 100
        }
      },
      {
        "table": {
          "table_name": "tt2",
          "access_type": "ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["c1"],
          "ref": ["db1.tt1.c1"],
          "loops": 100,
          "rows": 1,
          "cost": 0.0972762,
          "filtered": 100
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
#
# Equi-Join query on a single table having 1 primary key index on 2 columns.
# However, only 1 column from the index that has non-unique values is used in the condition
#
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1, t1 as tt2 where tt1.c2 = tt2.c2
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.9890562,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 100
                }
            },
            {
                "block-nl-join": 
                {
                    "table": 
                    {
                        "table_name": "tt2",
                        "access_type": "ALL",
                        "loops": 100,
                        "rows": 100,
                        "cost": 0.9619014,
                        "filtered": 100
                    },
                    "buffer_type": "flat",
                    "buffer_size": "1008",
                    "join_type": "BNL",
                    "attached_condition": "tt2.c2 = tt1.c2"
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.9890562,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ALL",
          "loops": 1,
          "rows": 100,
          "cost": 0.0271548,
          "filtered": 100
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "tt2",
            "access_type": "ALL",
            "loops": 100,
            "rows": 100,
            "cost": 0.9619014,
            "filtered": 100
          },
          "buffer_type": "flat",
          "buffer_size": "1008",
          "join_type": "BNL",
          "attached_condition": "tt2.c2 = tt1.c2"
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
#
# Query on a single table having 1 primary key index on 2 columns.
# However, a constant literal is used in the equality predicate with only 1 column from the index that has unique values.
#
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1 where tt1.c1 = 5
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0017838,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ref",
                    "possible_keys": 
                    ["PRIMARY"],
                    "key": "PRIMARY",
                    "key_length": "4",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["const"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0017838,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.00178377,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ref",
          "possible_keys": ["PRIMARY"],
          "key": "PRIMARY",
          "key_length": "4",
          "used_key_parts": ["c1"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00178377,
          "filtered": 100
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
#
# Query on a single table having 1 primary key index on 2 columns.
# However, a constant literal is used in the equality predicate with only 1 column from the index that has non-unique values.
#
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1 where tt1.c2 = 5
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0271548,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 1,
                    "attached_condition": "tt1.c2 = 5"
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.0271548,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ALL",
          "loops": 1,
          "rows": 100,
          "cost": 0.0271548,
          "filtered": 1,
          "attached_condition": "tt1.c2 = 5"
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Query on a single table having 1 non-unique index on 2 columns.
# However, a constant literal is used in the equality predicate using only 1 column from the index.
#
create table t1 (
c1 int,
c2 int,
index(c1)
) Engine=InnoDB;
insert into t1 select seq%3, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1 where tt1.c1 = 3
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0034586,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ref",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["const"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0034586,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.00345856,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ref",
          "possible_keys": ["c1"],
          "key": "c1",
          "key_length": "5",
          "used_key_parts": ["c1"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00345856,
          "filtered": 100
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
#
# Query on a single table having 1 non-unique index on a single column.
# Also, a constant literal is used in the equality predicate on the column that is in the index.
#
insert into t1 select seq%3, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1 where tt1.c1 = 5
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0034586,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ref",
                    "possible_keys": 
                    ["c1"],
                    "key": "c1",
                    "key_length": "5",
                    "used_key_parts": 
                    ["c1"],
                    "ref": 
                    ["const"],
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0034586,
                    "filtered": 100
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.00345856,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ref",
          "possible_keys": ["c1"],
          "key": "c1",
          "key_length": "5",
          "used_key_parts": ["c1"],
          "ref": ["const"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00345856,
          "filtered": 100
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Query on a single table having 1 primary key index with only 1 column.
# However, a constant literal is used in the equality predicate on the column that is not in the index.
#
create table t1 (
c1 int,
c2 int,
primary key(c1)
) Engine=InnoDB;
insert into t1 select seq, seq%5 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1 where tt1.c2 = 4
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0271548,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "ALL",
                    "loops": 1,
                    "rows": 100,
                    "cost": 0.0271548,
                    "filtered": 20,
                    "attached_condition": "tt1.c2 = 4"
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.0271548,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "ALL",
          "loops": 1,
          "rows": 100,
          "cost": 0.0271548,
          "filtered": 20,
          "attached_condition": "tt1.c2 = 4"
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Index-Merge query on a single table having 2 non-unique index with a single column in each.
# Also, index column is used in the condition
#
create table t1 (
c1 int,
c2 int,
index(c1),
index(c2)
) Engine=InnoDB;
insert into t1 select seq%5, seq%10 from seq_1_to_100;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 as tt1 where tt1.c1 = 5 OR tt1.c2 = 10
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0077168,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "tt1",
                    "access_type": "index_merge",
                    "possible_keys": 
                    [
                        "c1",
                        "c2"
                    ],
                    "key_length": "5,5",
                    "index_merge": 
                    {
                        "union": 
                        [
                            {
                                "range": 
                                {
                                    "key": "c1",
                                    "used_key_parts": 
                                    ["c1"]
                                }
                            },
                            {
                                "range": 
                                {
                                    "key": "c2",
                                    "used_key_parts": 
                                    ["c2"]
                                }
                            }
                        ]
                    },
                    "loops": 1,
                    "rows": 2,
                    "cost": 0.0077168,
                    "filtered": 100,
                    "attached_condition": "tt1.c1 = 5 or tt1.c2 = 10"
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.007716836,
    "nested_loop": [
      {
        "table": {
          "table_name": "tt1",
          "access_type": "index_merge",
          "possible_keys": ["c1", "c2"],
          "key_length": "5,5",
          "index_merge": {
            "union": [
              {
                "range": {
                  "key": "c1",
                  "used_key_parts": ["c1"]
                }
              },
              {
                "range": {
                  "key": "c2",
                  "used_key_parts": ["c2"]
                }
              }
            ]
          },
          "loops": 1,
          "rows": 2,
          "cost": 0.007716836,
          "filtered": 100,
          "attached_condition": "tt1.c1 = 5 or tt1.c2 = 10"
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
#
# Index-Merge query on a single table having 2 indexes with overlapping keys
#
create table t1 (
a int,
b int,
c int,
index idx_ab(a, b),
index idx_ac(a, c)
) Engine=InnoDB;
insert into t1 select seq%2, seq%3, seq%5 from seq_1_to_20;
analyze table t1;
Table Op Msg_type Msg_text
db1.t1 analyze status Engine-independent statistics collected
db1.t1 analyze status OK
set optimizer_replay_context= "";
explain format=json select * from t1 where a=1 and b=1 and c=1
set optimizer_record_context= ON;
select context into dumpfile
"../../tmp/dump1.sql" from information_schema.optimizer_context;
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select @explain_output;
@explain_output
{
    "query_block": 
    {
        "select_id": 1,
        "cost": 0.0038076,
        "nested_loop": 
        [
            {
                "table": 
                {
                    "table_name": "t1",
                    "access_type": "index_merge",
                    "possible_keys": 
                    [
                        "idx_ab",
                        "idx_ac"
                    ],
                    "key_length": "10,10",
                    "index_merge": 
                    {
                        "intersect": 
                        [
                            {
                                "range": 
                                {
                                    "key": "idx_ac",
                                    "used_key_parts": 
                                    [
                                        "a",
                                        "c"
                                    ]
                                }
                            },
                            {
                                "range": 
                                {
                                    "key": "idx_ab",
                                    "used_key_parts": 
                                    [
                                        "a",
                                        "b"
                                    ]
                                }
                            }
                        ]
                    },
                    "loops": 1,
                    "rows": 1,
                    "cost": 0.0038076,
                    "filtered": 35,
                    "attached_condition": "t1.a = 1 and t1.b = 1 and t1.c = 1",
                    "using_index": true
                }
            }
        ]
    }
}
set @saved_explain_output= @explain_output;
drop table t1;
#source the dump1.sql file
Warnings:
Warning 4200 The setting 'optimizer_adjust_secondary_key_costs' is ignored. It only exists for compatibility with old installations and will be removed in a future release
Warnings:
Note 1007 Can't create database 'db1'; database exists
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.00380755,
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "index_merge",
          "possible_keys": ["idx_ab", "idx_ac"],
          "key_length": "10,10",
          "index_merge": {
            "intersect": [
              {
                "range": {
                  "key": "idx_ac",
                  "used_key_parts": ["a", "c"]
                }
              },
              {
                "range": {
                  "key": "idx_ab",
                  "used_key_parts": ["a", "b"]
                }
              }
            ]
          },
          "loops": 1,
          "rows": 1,
          "cost": 0.00380755,
          "filtered": 35,
          "attached_condition": "t1.a = 1 and t1.b = 1 and t1.c = 1",
          "using_index": true
        }
      }
    ]
  }
}
set optimizer_replay_context= 'opt_context';
set @explain_output= '$explain_output';
set @explain_output= (select json_pretty(round_cost(@explain_output)));
select JSON_EQUALS(@saved_explain_output, @explain_output);
JSON_EQUALS(@saved_explain_output, @explain_output)
1
set optimizer_replay_context= "";
drop table t1;
drop function round_cost;
drop database db1;