[MDEV-33890] For unique hash keys or for IDKU INDEX_STATISTICS counts INSERTs - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Trivial
Resolution: Unresolved
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4, 11.5(EOL)
Fix Version/s: 10.5, 10.6, 10.11, 11.4
Component/s: Plugin - userstat
Labels:
None

Description

For unique hash keys (but not long ones), INDEX_STATISTICS is updated when the table is INSERT-ed into, instead of SELECTs for which the keys are rarely, if ever, used. It can give a user a wrong idea about efficiency of indexes.

--source include/have_sequence.inc

SET @userstat.save= @@userstat;

set global userstat= 1;

create table t (a int, unique(a) using hash) engine=MyISAM;

flush index_statistics;

insert into t select seq from seq_1_to_100;

select * from information_schema.index_statistics where table_name = 't';

insert into t select seq from seq_101_to_200;

select * from information_schema.index_statistics where table_name = 't';

explain select a from t where a = 10;

select a from t where a = 10;

select * from information_schema.index_statistics where table_name = 't';

# Cleanup

drop table t;

set global userstat= @userstat.save;

10.4 662bb176b412993a085fe329af559ddc3dc83ec3
insert into t select seq from seq_1_to_100;
select * from information_schema.index_statistics where table_name = 't';
TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ
test t a 4
insert into t select seq from seq_101_to_200;
select * from information_schema.index_statistics where table_name = 't';
TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ
test t a 48
explain select a from t where a = 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ALL a NULL NULL NULL 200 Using where
select a from t where a = 10;
a
10
select * from information_schema.index_statistics where table_name = 't';
TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ
test t a 48

Attachments

Activity

Elena Stepanova added a comment - 2024-04-12 17:00

Similar effect upon insert ... on duplicate key update (but not on insert ignore and not on update):

--source include/have_innodb.inc

SET @userstat.save= @@userstat;

set global userstat= 1;

create table t (a int, unique(a)) engine=InnoDB;

insert into t values (1),(2),(3),(4);

flush index_statistics;

insert ignore into t values (1),(2),(3),(4);

show index_statistics;

insert into t values (1),(2),(3),(4) on duplicate key update a = a + 10;

show index_statistics;

update t set a = a + 10;

show index_statistics;

# Cleanup

set global userstat= @userstat.save;

drop table t;

10.4 d8249775980f9a6cb1a85e4799e1c1be452c2f2a
flush index_statistics;
insert ignore into t values (1),(2),(3),(4);
Warnings:
Warning 1062 Duplicate entry '1' for key 'a'
Warning 1062 Duplicate entry '2' for key 'a'
Warning 1062 Duplicate entry '3' for key 'a'
Warning 1062 Duplicate entry '4' for key 'a'
show index_statistics;
Table_schema Table_name Index_name Rows_read
insert into t values (1),(2),(3),(4) on duplicate key update a = a + 10;
show index_statistics;
Table_schema Table_name Index_name Rows_read
test t a 4
update t set a = a + 10;
show index_statistics;
Table_schema Table_name Index_name Rows_read
test t a 4
set global userstat= @userstat.save;

Elena Stepanova added a comment - 2024-04-12 17:00 Similar effect upon insert ... on duplicate key update (but not on insert ignore and not on update ): --source include/have_innodb.inc SET @userstat.save= @@userstat; set global userstat= 1; create table t (a int , unique (a)) engine=InnoDB; insert into t values (1),(2),(3),(4); flush index_statistics; insert ignore into t values (1),(2),(3),(4); show index_statistics; insert into t values (1),(2),(3),(4) on duplicate key update a = a + 10; show index_statistics; update t set a = a + 10; show index_statistics; # Cleanup set global userstat= @userstat.save; drop table t; 10.4 d8249775980f9a6cb1a85e4799e1c1be452c2f2a flush index_statistics; insert ignore into t values (1),(2),(3),(4); Warnings: Warning 1062 Duplicate entry '1' for key 'a' Warning 1062 Duplicate entry '2' for key 'a' Warning 1062 Duplicate entry '3' for key 'a' Warning 1062 Duplicate entry '4' for key 'a' show index_statistics; Table_schema Table_name Index_name Rows_read insert into t values (1),(2),(3),(4) on duplicate key update a = a + 10; show index_statistics; Table_schema Table_name Index_name Rows_read test t a 4 update t set a = a + 10; show index_statistics; Table_schema Table_name Index_name Rows_read test t a 4 set global userstat= @userstat.save;

People

Assignee:: Michael Widenius

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2024-04-11 17:43

Updated:: 2024-11-22 17:12

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server