mirror of
https://github.com/MariaDB/server.git
synced 2025-04-03 13:55:38 +02:00
43c5d1303f
Backport of commit 74f70c3944 to 10.11.
The new logic is disabled by default, to enable, use
optimizer_adjust_secondary_key_costs=fix_derived_table_read_cost.
== Original commit comment ==
Fixed costs in JOIN_TAB::estimate_scan_time() and HEAP
Estimate_scan_time() calculates the cost of scanning a derivied table.
The old code did not take into account that the temporary table heap table
may be converted to Aria.
Things fixed:
- Added checking if the temporary tables data will fit in the heap.
If not, then calculate the cost based on the designated internal
temporary table engine (Aria).
- Removed MY_MAX(records, 1000) and instead trust the optimizer's
estimate of records. This reduces the cost of temporary tables a bit
for small tables, which caused a few changes in mtr results.
- Fixed cost calculation for HEAP.
- HEAP costs->row_next_find_cost was not set. This does not affect old
costs calculation as this cost slot was not used anywhere.
Now HEAP cost->row_next_find_cost is set, which allowed me to remove
some duplicated computation in ha_heap::scan_time()
94 lines
2.9 KiB
Text
94 lines
2.9 KiB
Text
--source include/have_sequence.inc
|
|
--source include/not_embedded.inc
|
|
--source include/have_innodb.inc
|
|
|
|
#
|
|
# Show the costs for rowid filter
|
|
#
|
|
|
|
create table t1 (
|
|
pk int primary key auto_increment,
|
|
nm varchar(32),
|
|
fl1 tinyint default 0,
|
|
fl2 tinyint default 0,
|
|
index idx1(nm, fl1),
|
|
index idx2(fl2)
|
|
) engine=myisam charset=latin1;
|
|
|
|
create table name (
|
|
pk int primary key auto_increment,
|
|
nm bigint
|
|
) engine=myisam;
|
|
|
|
create table flag2 (
|
|
pk int primary key auto_increment,
|
|
fl2 tinyint
|
|
) engine=myisam;
|
|
|
|
insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
|
|
insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
|
|
|
|
insert into t1(nm,fl2)
|
|
select nm, fl2 from name, flag2 where name.pk = flag2.pk;
|
|
|
|
analyze table t1 persistent for all;
|
|
|
|
--disable_ps_protocol
|
|
set optimizer_trace="enabled=on";
|
|
set optimizer_switch='rowid_filter=on';
|
|
set statement optimizer_adjust_secondary_key_costs=0 for
|
|
explain select * from t1 where nm like '500%' AND fl2 = 0;
|
|
set @trace=(select trace from information_schema.optimizer_trace);
|
|
select json_detailed(json_extract(@trace, '$**.considered_access_paths'));
|
|
|
|
--echo
|
|
--echo The following trace should have a different rowid_filter_key cost
|
|
--echo
|
|
set statement optimizer_adjust_secondary_key_costs=2 for
|
|
explain select * from t1 where nm like '500%' AND fl2 = 0;
|
|
set @trace=(select trace from information_schema.optimizer_trace);
|
|
select json_detailed(json_extract(@trace, '$**.considered_access_paths'));
|
|
|
|
--enable_ps_protocol
|
|
|
|
drop table t1, name, flag2;
|
|
select @@optimizer_adjust_secondary_key_costs;
|
|
set @@optimizer_adjust_secondary_key_costs=7;
|
|
select @@optimizer_adjust_secondary_key_costs;
|
|
set @@optimizer_adjust_secondary_key_costs=default;
|
|
|
|
--echo #
|
|
--echo # MDEV-34664: fix_innodb_cardinality
|
|
--echo #
|
|
|
|
set @save_userstat=@@global.userstat;
|
|
set @save_ispsp=@@global.innodb_stats_persistent_sample_pages;
|
|
set @@global.innodb_stats_persistent_sample_pages=20;
|
|
set @@global.userstat=on;
|
|
set use_stat_tables=PREFERABLY_FOR_QUERIES;
|
|
|
|
create or replace table t1 (a int primary key, b int, c int, d int, key(b,c,d)) engine=innodb;
|
|
insert into t1 select seq,seq/100,seq/60,seq/10 from seq_1_to_1000;
|
|
create or replace table t2 (a int);
|
|
insert into t2 values (1),(2),(3);
|
|
analyze table t1;
|
|
select count(distinct b),count(distinct b,c), count(distinct b,c,d) from t1;
|
|
show index from t1;
|
|
explain select * from t1,t2 where t1.b=t2.a;
|
|
set @@optimizer_adjust_secondary_key_costs=8;
|
|
explain select * from t1,t2 where t1.b=t2.a;
|
|
show index from t1;
|
|
# Flush tables or show index is needed to refresh the data in table share
|
|
flush tables;
|
|
explain select * from t1,t2 where t1.b=t2.a;
|
|
show index from t1;
|
|
# Check that the option does not affect other usage
|
|
connect (user2, localhost, root,,);
|
|
show index from t1;
|
|
connection default;
|
|
disconnect user2;
|
|
drop table t1,t2;
|
|
set global userstat=@save_userstat;
|
|
set global innodb_stats_persistent_sample_pages=@save_ispsp;
|
|
|
|
set @@optimizer_adjust_secondary_key_costs=default;
|