mirror of
https://github.com/MariaDB/server.git
synced 2025-06-10 06:44:37 +02:00
1b934a387c
This is needed to make it easy for users to automatically ignore long char and varchars when using ANALYZE TABLE PERSISTENT. These fields can cause problems as they will consume 'CHARACTERS * MAX_CHARACTER_LENGTH * 2 * number_of_rows' space on disk during analyze, which can easily be much bigger than the analyzed table. This commit adds a new user variable, analyze_max_length, default value 4G. Any field that is bigger than this in bytes, will be ignored by ANALYZE TABLE PERSISTENT unless it is specified in FOR COLUMNS(). While doing this patch, I noticed that we do not skip GEOMETRY columns from ANALYZE TABLE, like we do with BLOB. This should be fixed when merging to the 'main' branch. At the same time we should add a resonable default value for analyze_max_length, probably 1024, like we have for max_sort_length.
508 lines
19 KiB
Text
508 lines
19 KiB
Text
create table t1 (a bigint);
|
|
lock tables t1 write;
|
|
insert into t1 values(0);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
unlock tables;
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
create table t1 (a bigint);
|
|
insert into t1 values(0);
|
|
lock tables t1 write;
|
|
delete from t1;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
unlock tables;
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
create table t1 (a bigint);
|
|
insert into t1 values(0);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
create table t1 (a mediumtext, fulltext key key1(a)) charset utf8 collate utf8_general_ci engine myisam;
|
|
insert into t1 values ('hello');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'a'
|
|
test.t1 analyze status OK
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'a'
|
|
test.t1 analyze status Table is already up to date
|
|
drop table t1;
|
|
CREATE TABLE t1 (a int);
|
|
prepare stmt1 from "SELECT * FROM t1 PROCEDURE ANALYSE()";
|
|
execute stmt1;
|
|
Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
|
|
execute stmt1;
|
|
Field_name Min_value Max_value Min_length Max_length Empties_or_zeros Nulls Avg_value_or_avg_length Std Optimal_fieldtype
|
|
deallocate prepare stmt1;
|
|
drop table t1;
|
|
create temporary table t1(a int, index(a));
|
|
insert into t1 values('1'),('2'),('3'),('4'),('5');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
show index from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t1 1 a 1 a A 5 NULL NULL YES BTREE NO
|
|
drop table t1;
|
|
End of 4.1 tests
|
|
create table t1(a int);
|
|
analyze table t1 extended;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'extended' at line 1
|
|
optimize table t1 extended;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'extended' at line 1
|
|
drop table t1;
|
|
End of 5.0 tests
|
|
#
|
|
# Test analyze of text column (not yet supported)
|
|
#
|
|
set optimizer_use_condition_selectivity=4;
|
|
set histogram_type='single_prec_hb';
|
|
set histogram_size=255;
|
|
create table t1 (a int not null, t tinytext, tx text);
|
|
insert into t1 select seq+1, repeat('X',seq*5), repeat('X',seq*10) from seq_0_to_50;
|
|
insert into t1 select seq+100, repeat('X',5), "" from seq_1_to_10;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'tx'
|
|
test.t1 analyze status OK
|
|
explain select count(*) from t1 where t='XXXXXX';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 61 Using where
|
|
select column_name, min_value, max_value, hist_size from mysql.column_stats where table_name='t1';
|
|
column_name min_value max_value hist_size
|
|
a 1 110 255
|
|
drop table t1;
|
|
set use_stat_tables=default;
|
|
set histogram_type=default;
|
|
set histogram_size=default;
|
|
#
|
|
# MDEV-31957 Concurrent ALTER and ANALYZE collecting statistics can
|
|
# result in stale statistical data
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(128));
|
|
INSERT INTO t1 SELECT seq, CONCAT('s',seq) FROM seq_1_to_100;
|
|
connect con1,localhost,root,,;
|
|
ALTER TABLE t1 MODIFY b BLOB;
|
|
connection default;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
connection con1;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
connection default;
|
|
disconnect con1;
|
|
select db_name,table_name,column_name from mysql.column_stats;
|
|
db_name table_name column_name
|
|
test t1 a
|
|
drop table t1;
|
|
#
|
|
# Testing swapping columns
|
|
#
|
|
create or replace table t1 (a int primary key, b varchar(100), c varchar(100), d varchar(100)) engine=innodb;
|
|
insert into t1 select seq, repeat('b',seq),repeat('c',mod(seq,5)), repeat('d',mod(seq,10)) from seq_1_to_100;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 b 50.5000
|
|
test t1 c 2.0000
|
|
test t1 d 4.5000
|
|
alter table t1 change b c varchar(200), change c b varchar(200);
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 b 2.0000
|
|
test t1 c 50.5000
|
|
test t1 d 4.5000
|
|
alter table t1 change b c varchar(200), change c d varchar(200), change d b varchar(200) ;
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 b 4.5000
|
|
test t1 c 2.0000
|
|
test t1 d 50.5000
|
|
alter table t1 change b c varchar(200), change c d varchar(200), change d e varchar(200) ;
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 c 4.5000
|
|
test t1 d 2.0000
|
|
test t1 e 50.5000
|
|
alter table t1 change e d varchar(200), drop column d;
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 c 4.5000
|
|
test t1 d 50.5000
|
|
# Test having non existing column in column_stats
|
|
insert into mysql.column_stats (db_name,table_name,column_name) values ("test","t1","b");
|
|
alter table t1 change c d varchar(200), change d b varchar(200);
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 b 50.5000
|
|
test t1 d 4.5000
|
|
# Test having a conflicting temporary name
|
|
insert into mysql.column_stats (db_name,table_name,column_name) values ("test","t1",concat("#sql_tmp_name#1",char(0)));
|
|
alter table t1 change d b varchar(200), change b d varchar(200);
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 b 4.5000
|
|
test t1 d 50.5000
|
|
drop table t1;
|
|
truncate table mysql.column_stats;
|
|
create or replace table t1 (a int primary key, b varchar(100), c varchar(100), d varchar(100)) engine=myisam;
|
|
insert into t1 select seq, repeat('b',seq),repeat('c',mod(seq,5)), repeat('d',mod(seq,10)) from seq_1_to_100;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 b 50.5000
|
|
test t1 c 2.0000
|
|
test t1 d 4.5000
|
|
alter table t1 change b c varchar(200), change c b varchar(200);
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 d 4.5000
|
|
analyze table t1 persistent for columns(b,c) indexes all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
alter table t1 change b c varchar(200), change c d varchar(200), change d b varchar(200) ;
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 b 50.5000
|
|
test t1 c 2.0000
|
|
analyze table t1 persistent for columns(d) indexes all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
alter table t1 change b c varchar(200), change c d varchar(200), change d e varchar(200) ;
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 c 50.5000
|
|
test t1 d 2.0000
|
|
test t1 e 50.5000
|
|
alter table t1 change e d varchar(200), drop column d;
|
|
select db_name,table_name,column_name,avg_length from mysql.column_stats order by column_name;
|
|
db_name table_name column_name avg_length
|
|
test t1 a 4.0000
|
|
test t1 c 50.5000
|
|
test t1 d 50.5000
|
|
drop table t1;
|
|
truncate table mysql.column_stats;
|
|
create table t1 (a int, b blob, unique(b)) engine= innodb;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'b'
|
|
test.t1 analyze status OK
|
|
select column_name from mysql.column_stats where table_name = 't1';
|
|
column_name
|
|
a
|
|
drop table t1;
|
|
create table t1 (a int, b blob, c int generated always as (length(b)) virtual) engine= innodb;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'b'
|
|
test.t1 analyze status OK
|
|
select column_name from mysql.column_stats where table_name = 't1';
|
|
column_name
|
|
a
|
|
c
|
|
drop table t1;
|
|
CREATE or replace TABLE t1 (a INT, b CHAR(8));
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
ALTER TABLE t1 CHANGE b c INT, ORDER BY b;
|
|
SELECT db_name, table_name, column_name FROM mysql.column_stats where table_name = 't1';
|
|
db_name table_name column_name
|
|
test t1 a
|
|
test t1 c
|
|
drop table t1;
|
|
CREATE or replace TABLE t1 (a INT, b CHAR(8));
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
ALTER TABLE t1 RENAME COLUMN b to c, ALGORITHM=COPY;
|
|
SELECT db_name, table_name, column_name FROM mysql.column_stats where table_name = 't1';
|
|
db_name table_name column_name
|
|
test t1 a
|
|
test t1 c
|
|
drop table t1;
|
|
#
|
|
# Testing swapping indexes
|
|
#
|
|
create or replace table t1 (a int primary key, b varchar(100), c varchar(100), d varchar(100), index (b), index(c), index(d,b)) engine=innodb;
|
|
insert into t1 select seq, repeat('b',seq),repeat('c',mod(seq,5)), repeat('d',mod(seq,10)) from seq_1_to_100;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select * from mysql.index_stats order by index_name, prefix_arity;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 b 1 1.0000
|
|
test t1 b 2 1.0000
|
|
test t1 c 1 20.0000
|
|
test t1 c 2 1.0000
|
|
test t1 d 1 10.0000
|
|
test t1 d 2 1.0000
|
|
test t1 d 3 1.0000
|
|
alter table t1 rename index b to c, rename index c to d, rename index d to b;
|
|
select * from mysql.index_stats order by index_name;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 b 1 10.0000
|
|
test t1 b 2 1.0000
|
|
test t1 b 3 1.0000
|
|
test t1 c 1 1.0000
|
|
test t1 c 2 1.0000
|
|
test t1 d 1 20.0000
|
|
test t1 d 2 1.0000
|
|
alter table t1 rename index b to c, rename index c to d, rename index d to e;
|
|
select * from mysql.index_stats order by index_name, prefix_arity;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 c 1 10.0000
|
|
test t1 c 2 1.0000
|
|
test t1 c 3 1.0000
|
|
test t1 d 1 1.0000
|
|
test t1 d 2 1.0000
|
|
test t1 e 1 20.0000
|
|
test t1 e 2 1.0000
|
|
alter table t1 rename index e to b;
|
|
alter table t1 change b c varchar(200), change c d varchar(200), change d e varchar(200) ;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`c` varchar(200) DEFAULT NULL,
|
|
`d` varchar(200) DEFAULT NULL,
|
|
`e` varchar(200) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `d` (`c`),
|
|
KEY `b` (`d`),
|
|
KEY `c` (`e`,`c`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
select * from mysql.index_stats order by index_name, prefix_arity;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 b 1 20.0000
|
|
test t1 b 2 1.0000
|
|
test t1 c 1 10.0000
|
|
test t1 c 2 1.0000
|
|
test t1 c 3 1.0000
|
|
test t1 d 1 1.0000
|
|
test t1 d 2 1.0000
|
|
# Test having a conflicting temporary name
|
|
insert into mysql.index_stats (db_name,table_name,index_name,prefix_arity) values ("test","t1",concat("#sql_tmp_name#1",char(0)),1);
|
|
alter table t1 rename index c to d, rename index d to c;
|
|
select * from mysql.index_stats order by index_name, prefix_arity;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 b 1 20.0000
|
|
test t1 b 2 1.0000
|
|
test t1 c 1 1.0000
|
|
test t1 c 2 1.0000
|
|
test t1 d 1 10.0000
|
|
test t1 d 2 1.0000
|
|
test t1 d 3 1.0000
|
|
drop table t1;
|
|
select * from mysql.index_stats order by index_name, prefix_arity;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
#
|
|
# Test of adding key that replaces foreign key
|
|
#
|
|
CREATE TABLE t1 (aaaa INT, b INT, KEY(b), FOREIGN KEY(aaaa) REFERENCES t1(b)) ENGINE=InnoDB;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
|
|
index_name
|
|
aaaa
|
|
b
|
|
ALTER TABLE t1 ADD KEY idx(aaaa);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`aaaa` int(11) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
KEY `b` (`b`),
|
|
KEY `idx` (`aaaa`),
|
|
CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`aaaa`) REFERENCES `t1` (`b`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
|
|
index_name
|
|
b
|
|
truncate table mysql.index_stats;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
SELECT index_name FROM mysql.index_stats WHERE table_name = 't1' order by index_name;
|
|
index_name
|
|
b
|
|
idx
|
|
ALTER TABLE t1 DROP KEY idx;
|
|
ERROR HY000: Cannot drop index 'idx': needed in a foreign key constraint
|
|
DROP TABLE t1;
|
|
#
|
|
# Check index rename where name is not changed
|
|
#
|
|
create or replace table t1 (a int primary key, b int, c int, key b (b,c));
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`,`c`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
select * from mysql.index_stats where table_name= "t1";
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 b 1 NULL
|
|
test t1 b 2 NULL
|
|
alter ignore table t1 rename key `b` to b, LOCK=shared;
|
|
select * from mysql.index_stats where table_name= "t1";
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 b 1 NULL
|
|
test t1 b 2 NULL
|
|
alter ignore table t1 rename key `b` to `B`, LOCK=shared;
|
|
select * from mysql.index_stats where table_name= "t1";
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 PRIMARY 1 1.0000
|
|
test t1 B 1 NULL
|
|
test t1 B 2 NULL
|
|
drop table t1;
|
|
#
|
|
# Crash inis_eits_usable()
|
|
#
|
|
CREATE TABLE t1 (a int) ENGINE=MyISAM;
|
|
CREATE TABLE t2 (b int) ENGINE=MyISAM;
|
|
INSERT INTO t1 (a) VALUES (4), (6);
|
|
INSERT INTO t2 (b) VALUES (0), (8);
|
|
set @save_join_cache_level=@@join_cache_level;
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
SET join_cache_level=3;
|
|
SET optimizer_switch='join_cache_hashed=on';
|
|
SET optimizer_switch='join_cache_bka=on';
|
|
set optimizer_switch='hash_join_cardinality=on';
|
|
EXPLAIN
|
|
SELECT * FROM t1, t2 WHERE b=a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
|
|
1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 2 Using where; Using join buffer (flat, BNLH join)
|
|
SELECT * FROM t1, t2 WHERE b=a;
|
|
a b
|
|
DROP TABLE t1,t2;
|
|
set @@optimizer_switch=@save_optimizer_switch;
|
|
set @@join_cache_level=@save_join_cache_level;
|
|
#
|
|
# MDEV-32531 MSAN / Valgrind errors in Item_func_like::get_mm_leaf with
|
|
# temporal field
|
|
#
|
|
CREATE TABLE t1 (f DATE);
|
|
INSERT INTO t1 VALUES ('1978-08-27'),('1906-04-30');
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
SELECT * FROM t1 WHERE f LIKE '2023%';
|
|
f
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-36536 Add option to not collect statistics for long char/varchars
|
|
#
|
|
select @@session.analyze_max_length;
|
|
@@session.analyze_max_length
|
|
4294967295
|
|
create table t1 (c0 char(2), c1 char(16), c2 char(64), v1 varchar(16), v2 varchar(1000), b1 blob, i1 int)
|
|
character set utf8mb4 COLLATE utf8mb4_bin;
|
|
insert into t1 values ("A", "A","A","A","A","A",1), ("B","B","B","B","B","B",1);
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'b1'
|
|
test.t1 analyze status OK
|
|
select column_name from mysql.column_stats where table_name = 't1';
|
|
column_name
|
|
c0
|
|
c1
|
|
c2
|
|
i1
|
|
v1
|
|
v2
|
|
set @@session.analyze_max_length= 64;
|
|
truncate table mysql.column_stats;
|
|
ANALYZE TABLE t1 PERSISTENT FOR ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'c2'
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'v2'
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 'b1'
|
|
test.t1 analyze status Table is already up to date
|
|
select column_name from mysql.column_stats where table_name = 't1';
|
|
column_name
|
|
c0
|
|
c1
|
|
i1
|
|
v1
|
|
truncate table mysql.column_stats;
|
|
ANALYZE TABLE t1 PERSISTENT for COLUMNS (c0,c2,v1,v2,i1) INDEXES ALL;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
select column_name from mysql.column_stats where table_name = 't1';
|
|
column_name
|
|
c0
|
|
c2
|
|
i1
|
|
v1
|
|
v2
|
|
set @@session.analyze_max_length= default;
|
|
drop table t1;
|
|
#
|
|
# End of 10.6 tests
|
|
#
|