198 lines
7.6 KiB
Plaintext
198 lines
7.6 KiB
Plaintext
=====
|
|
=== Test ANALYZE behavior after default creation
|
|
=====
|
|
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
1
|
|
DROP TABLE test_ps_flag;
|
|
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=default;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
1
|
|
DROP TABLE test_ps_flag;
|
|
=====
|
|
=== Test ANALYZE behavior after creation with explicit PS=OFF
|
|
=====
|
|
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=0;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
0
|
|
DROP TABLE test_ps_flag;
|
|
=====
|
|
=== Test ANALYZE behavior after creation with explicit PS=ON
|
|
=====
|
|
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=1;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=1
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
1
|
|
DROP TABLE test_ps_flag;
|
|
=====
|
|
=== Test ANALYZE behavior after creation with explicit PS=OFF,
|
|
=== then ALTER to ON, then ALTER to OFF, then ALTER to default
|
|
=====
|
|
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=0;
|
|
ALTER TABLE test_ps_flag STATS_PERSISTENT=1;
|
|
# restart
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=1
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
1
|
|
ALTER TABLE test_ps_flag STATS_PERSISTENT=0;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
0
|
|
ALTER TABLE test_ps_flag STATS_PERSISTENT=default;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
1
|
|
DROP TABLE test_ps_flag;
|
|
=====
|
|
=== Test ANALYZE behavior after creation with explicit PS=ON,
|
|
=== then ALTER to OFF, then ALTER to ON, then ALTER to default
|
|
=====
|
|
CREATE TABLE test_ps_flag (a INT) ENGINE=INNODB STATS_PERSISTENT=1;
|
|
ALTER TABLE test_ps_flag STATS_PERSISTENT=0;
|
|
# restart
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
0
|
|
ALTER TABLE test_ps_flag STATS_PERSISTENT=1;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=1
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
1
|
|
ALTER TABLE test_ps_flag STATS_PERSISTENT=default;
|
|
SHOW CREATE TABLE test_ps_flag;
|
|
Table Create Table
|
|
test_ps_flag CREATE TABLE `test_ps_flag` (
|
|
`a` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DELETE FROM mysql.innodb_index_stats WHERE table_name = 'test_ps_flag';
|
|
DELETE FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
SELECT COUNT(*) AS cnt_before FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_before
|
|
0
|
|
ANALYZE TABLE test_ps_flag;
|
|
Table Op Msg_type Msg_text
|
|
test.test_ps_flag analyze status OK
|
|
SELECT COUNT(*) AS cnt_after FROM mysql.innodb_table_stats WHERE table_name = 'test_ps_flag';
|
|
cnt_after
|
|
1
|
|
DROP TABLE test_ps_flag;
|