--echo # --echo # BUG#30837086 : SELECT FROM I_S.INNODB_COLUMNS IS SLOW IN 8.0 --echo # --echo # Table without partition and virual columns CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d INT, c INT AS(a+b)); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT AS(a+b), d INT); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, c INT AS(a+b), b INT, d INT); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; CREATE TABLE t1 (c INT AS(a+b), a INT NOT NULL PRIMARY KEY, b INT, d INT); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT AS(a+b), d INT, e INT AS(b+d) STORED,f INT AS(a+b+d)); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; --echo # Table with partition and virtual column CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d INT, c INT AS(a+b)) PARTITION BY HASH (a) PARTITIONS 3; --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p0'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p2'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT AS(a+b), d INT, e INT AS(b+d) STORED,f INT AS(a+b+d)) PARTITION BY HASH (a) PARTITIONS 3; --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p0'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p2'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; --echo # Table with FTS Index --error 1214 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d VARCHAR(100), c INT AS(a+b), FULLTEXT idx (d)) PARTITION BY HASH (a) PARTITIONS 3; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d VARCHAR(100), c INT AS(a+b), FULLTEXT idx (d)); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1; --echo # Table with functional index CREATE TABLE t1 (int_col INTEGER, string_col VARCHAR(255)); CREATE INDEX int_func_index ON t1 ((ABS(int_col))); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log CREATE INDEX string_func_index ON t1 ((SUBSTRING(string_col, 1, 2))); --disable_query_log let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`; eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id; --enable_query_log DROP TABLE t1;