mirror of
https://github.com/MariaDB/server.git
synced 2026-02-08 19:58:40 +01:00
8647354f6b
(Cherry-pick into 12.2: the fix is enabled by default, control via @@new_mode=FIX_INDEX_LOOKUP_COST is removed) They were based on the maximum possible length key length, which can be much larger than the actual data. 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().
36 lines
967 B
Text
36 lines
967 B
Text
#
|
|
# MDEV-38164: Poor cost calculations for index access cause bad query plans for big VARCHARs in 11.x
|
|
#
|
|
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;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
# This should use ref access, not full scan:
|
|
explain select * from t1 where pk1=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref PRIMARY PRIMARY 4 # 1250
|
|
drop table t1;
|