Details
-
Bug
-
Status: Open (View Workflow)
-
Trivial
-
Resolution: Unresolved
-
10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4, 11.5
-
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
|