mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
457 lines
17 KiB
Text
457 lines
17 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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_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;
|
|
#
|
|
# End of 10.6 tests
|
|
#
|