path: root/mysql-test/main/analyze.result

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;
#
# End of 10.6 tests
#