[Enhancement]: information_schema table has no index, SQL query retrieval is very slow.

[vzong]

Enhancement OceanBase CE 3.1.4

mysql> SELECT count(1) -> FROM information_schema.STATISTICS S -> WHERE TABLE_SCHEMA='xxxxxxard04db' -> AND TABLE_NAME='managexxxxxl_05' ; +----------+ | count(1) | +----------+ | 13 | +----------+ 1 row in set (3 min 19.53 sec)

CREATE VIEW STATISTICS AS SELECT 'def' as TABLE_CATALOG, table_schema AS TABLE_SCHEMA, table as TABLE_NAME, non_unique AS NON_UNIQUE, index_schema as INDEX_SCHEMA, key_name as INDEX_NAME, seq_in_index as SEQ_IN_INDEX, column_name as COLUMN_NAME, collation as COLLATION, cardinality as CARDINALITY, sub_part as SUB_PART, packed as PACKED, null as NULLABLE, index_type as INDEX_TYPE, COMMENT, index_comment as INDEX_COMMENT, is_visible as IS_VISIBLE FROM oceanbase.__tenant_virtual_table_index

CREATE TABLE __tenant_virtual_table_index ( table_id bigint(20) NOT NULL, key_name varchar(128) NOT NULL DEFAULT '', seq_in_index bigint(20) NOT NULL DEFAULT '0', table_schema varchar(128) NOT NULL DEFAULT '', table varchar(256) NOT NULL DEFAULT '', non_unique bigint(20) NOT NULL DEFAULT '0', index_schema varchar(128) NOT NULL DEFAULT '', column_name varchar(128) NOT NULL DEFAULT '', collation varchar(128) DEFAULT NULL, cardinality bigint(20) DEFAULT NULL, sub_part varchar(256) DEFAULT NULL, packed varchar(256) DEFAULT NULL, null varchar(128) NOT NULL DEFAULT '', index_type varchar(128) NOT NULL DEFAULT '', comment varchar(4096) DEFAULT NULL, index_comment varchar(4096) NOT NULL DEFAULT '', is_visible varchar(3) NOT NULL DEFAULT '', PRIMARY KEY (table_id, key_name, seq_in_index) ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'none' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 TABLEGROUP = 'oceanbase'

__tenant_virtual_table_index just have a primary key.

As the same, TABLE_CONSTRAINTS also has no index.

CREATE TABLE TABLE_CONSTRAINTS ( CONSTRAINT_CATALOG varchar(4096) NOT NULL DEFAULT '', CONSTRAINT_SCHEMA varchar(128) NOT NULL DEFAULT '', CONSTRAINT_NAME varchar(128) NOT NULL DEFAULT '', TABLE_SCHEMA varchar(128) NOT NULL DEFAULT '', TABLE_NAME varchar(256) NOT NULL DEFAULT '', CONSTRAINT_TYPE varchar(128) NOT NULL DEFAULT '' ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'none' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10

[hnwyllmm]

A good issue. Thanks for your report.