mirror of
https://github.com/MariaDB/server.git
synced 2025-04-22 15:15:41 +02:00
6d1f1b61b5
Ever since commit 007f68c37f,
ALTER TABLE no longer invokes handler::open() after
handler::commit_inplace_alter_table().
ha_innobase::reload_statistics(): Reload or recompute statistics
after ALTER TABLE.
innodb_notify_tabledef_changed(): A new function to invoke
ha_innobase::reload_statistics().
handlerton::notify_tabledef_changed(): Add the parameter handler*
so that ha_innobase::reload_statistics() can be invoked.
ha_partition::notify_tabledef_changed(),
partition_notify_tabledef_changed(): Pass through the call
to any partitions or subpartitions.
This is based on code that was supplied by Monty.
139 lines
5 KiB
Text
139 lines
5 KiB
Text
CREATE TABLE t (
|
|
a INT,
|
|
b INT,
|
|
c INT GENERATED ALWAYS AS(a+b),
|
|
d INT GENERATED ALWAYS AS(a+b+b),
|
|
KEY idxa (a),
|
|
KEY vidxcd (c, d)
|
|
) ENGINE=INNODB STATS_PERSISTENT=1 STATS_AUTO_RECALC=1;
|
|
INSERT INTO t (a,b) VALUES (1, 2);
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxa n_diff_pfx01 a
|
|
idxa n_diff_pfx02 a,DB_ROW_ID
|
|
idxa n_leaf_pages Number of leaf pages in the index
|
|
idxa size Number of pages in the index
|
|
vidxcd n_diff_pfx01 c
|
|
vidxcd n_diff_pfx02 c,d
|
|
vidxcd n_diff_pfx03 c,d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
ALTER TABLE t ADD COLUMN e INT GENERATED ALWAYS AS(a+a+b), ADD INDEX idxb (b), ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxa n_diff_pfx01 a
|
|
idxa n_diff_pfx02 a,DB_ROW_ID
|
|
idxa n_leaf_pages Number of leaf pages in the index
|
|
idxa size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 c
|
|
vidxcd n_diff_pfx02 c,d
|
|
vidxcd n_diff_pfx03 c,d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
ALTER TABLE t DROP COLUMN c, DROP INDEX idxa, ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 d
|
|
vidxcd n_diff_pfx02 d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
ALTER TABLE t ADD INDEX vidxe (e), ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 d
|
|
vidxcd n_diff_pfx02 d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
vidxe n_diff_pfx01 e
|
|
vidxe n_diff_pfx02 e,DB_ROW_ID
|
|
vidxe n_leaf_pages Number of leaf pages in the index
|
|
vidxe size Number of pages in the index
|
|
ALTER TABLE t ADD COLUMN f INT GENERATED ALWAYS AS(a + a), ADD INDEX vidxf (f), ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 d
|
|
vidxcd n_diff_pfx02 d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
vidxe n_diff_pfx01 e
|
|
vidxe n_diff_pfx02 e,DB_ROW_ID
|
|
vidxe n_leaf_pages Number of leaf pages in the index
|
|
vidxe size Number of pages in the index
|
|
vidxf n_diff_pfx01 f
|
|
vidxf n_diff_pfx02 f,DB_ROW_ID
|
|
vidxf n_leaf_pages Number of leaf pages in the index
|
|
vidxf size Number of pages in the index
|
|
ALTER TABLE t DROP INDEX vidxcd;
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxe n_diff_pfx01 e
|
|
vidxe n_diff_pfx02 e,DB_ROW_ID
|
|
vidxe n_leaf_pages Number of leaf pages in the index
|
|
vidxe size Number of pages in the index
|
|
vidxf n_diff_pfx01 f
|
|
vidxf n_diff_pfx02 f,DB_ROW_ID
|
|
vidxf n_leaf_pages Number of leaf pages in the index
|
|
vidxf size Number of pages in the index
|
|
DROP TABLE t;
|