ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
SET @save_enabled= @@GLOBAL.innodb_cmp_per_index_enabled;
SET GLOBAL innodb_cmp_per_index_enabled=ON;
SELECT * FROM information_schema.innodb_cmp_per_index;
CREATE TABLE t (
a INT,
b VARCHAR(512),
c VARCHAR(16),
PRIMARY KEY (a),
INDEX (b(512)),
INDEX (c(16))
) ENGINE=INNODB KEY_BLOCK_SIZE=2;
SELECT
database_name,
table_name,
index_name,
compress_ops,
compress_ops_ok,
uncompress_ops
FROM information_schema.innodb_cmp_per_index
ORDER BY 1, 2, 3;
database_name test
table_name t
index_name b
compress_ops 1
compress_ops_ok 1
uncompress_ops 0
database_name test
table_name t
index_name c
compress_ops 1
compress_ops_ok 1
uncompress_ops 0
database_name test
table_name t
index_name PRIMARY
compress_ops 1
compress_ops_ok 1
uncompress_ops 0
BEGIN;
COMMIT;
ALTER TABLE t DROP INDEX c;
GRANT USAGE ON *.* TO 'tuser01'@'localhost' IDENTIFIED BY 'cDJvI9s_Uq';
FLUSH PRIVILEGES;
connect  con1,localhost,tuser01,cDJvI9s_Uq,;
connection con1;
SELECT * FROM information_schema.innodb_cmp_per_index;
ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
connection default;
disconnect con1;
DROP USER 'tuser01'@'localhost';
SELECT
database_name,
table_name,
index_name,
compress_ops,
compress_ops_ok,
uncompress_ops
FROM information_schema.innodb_cmp_per_index
ORDER BY 1, 2, 3;
database_name test
table_name t
index_name b
compress_ops 52
compress_ops_ok 52
uncompress_ops 0
database_name test
table_name t
index_name PRIMARY
compress_ops 64
compress_ops_ok 64
uncompress_ops 0
DROP TABLE t;
SET GLOBAL innodb_cmp_per_index_enabled=@save_enabled;
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;