mirror of
https://github.com/MariaDB/server.git
synced 2026-01-26 05:21:36 +01:00
4cff562f3f
They were based on the maximum possible key tuple length, which can be much larger than the real data size. The return value is used by handler::keyread_time(), which is used to estimate the cost of range access. This could cause range access not to be picked, even if it uses the clustered PK and reads about 8% of the table. The fix is to add KEY::stat_storage_length (next to KEY::rec_per_key) and have the storage engine fill it in handler::info(HA_STATUS_CONST). Currently, only InnoDB fills this based on its internal statistics: index->stat_index_size and ib_table->stat_n_rows. Also changed: - In handler::calculate_costs(), use ha_keyread_clustered_time() when computing clustered PK read cost, not ha_keyread_time(). The fix is OFF by default and enabled by setting FIX_INDEX_LOOKUP_COST flag in @@new_mode.
44 lines
1.1 KiB
Text
44 lines
1.1 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
|
|
--echo #
|
|
--echo # MDEV-38164: Poor cost calculations for index access cause bad query plans for big VARCHARs in 11.x
|
|
--echo #
|
|
create table t1 (
|
|
pk1 int not null,
|
|
pk2 int not null,
|
|
filler1 varchar(500),
|
|
filler2 varchar(500),
|
|
filler3 varchar(500),
|
|
filler4 varchar(500),
|
|
filler5 varchar(500),
|
|
filler6 varchar(500),
|
|
filler7 varchar(500),
|
|
filler8 varchar(500),
|
|
filler9 varchar(500),
|
|
fillerA varchar(500),
|
|
fillerB varchar(500),
|
|
fillerC varchar(500),
|
|
fillerD varchar(500),
|
|
fillerE varchar(500),
|
|
fillerF varchar(500),
|
|
primary key(pk1, pk2)
|
|
) collate utf8mb4_general_ci engine=innodb;
|
|
|
|
insert into t1 (pk1, pk2) select
|
|
mod(seq, 8), seq
|
|
from
|
|
seq_1_to_10000;
|
|
analyze table t1;
|
|
|
|
--echo # Before the fix: this will use full table scan:
|
|
--replace_column 8 #
|
|
explain select * from t1 where pk1=1;
|
|
|
|
set @save_new_mode=@@new_mode;
|
|
set new_mode=concat(@@new_mode,'FIX_INDEX_LOOKUP_COST');
|
|
--replace_column 8 #
|
|
explain select * from t1 where pk1=1;
|
|
|
|
set new_mode=@save_new_mode;
|
|
drop table t1;
|