greptimedb/tests/cases/standalone/common/show/show_index.sql

CREATE TABLE IF NOT EXISTS system_metrics (
    host STRING,
    idc STRING FULLTEXT INDEX INVERTED INDEX,
    cpu_util DOUBLE,
    memory_util DOUBLE,
    disk_util DOUBLE,
    desc1 STRING,
    desc2 STRING FULLTEXT INDEX,
    desc3 STRING FULLTEXT INDEX,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(host, idc),
    TIME INDEX(ts)
);

CREATE TABLE IF NOT EXISTS test (
    a STRING,
    b STRING SKIPPING INDEX,
    c DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(a, b),
    TIME INDEX(ts)
);

CREATE TABLE IF NOT EXISTS test_no_inverted_index (
    a STRING,
    b STRING SKIPPING INDEX,
    c DOUBLE,
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(a, b),
    TIME INDEX(ts)
);

show create table test_no_inverted_index;

SHOW INDEX;

SHOW INDEX FROM test;

SHOW INDEX FROM test_no_inverted_index;

SHOW INDEX FROM system_metrics;

SHOW INDEX FROM system_metrics in public;

SHOW INDEX FROM system_metrics like '%util%';

SHOW INDEX FROM system_metrics WHERE Key_name = 'TIME INDEX';

SELECT table_schema,
       table_name,
       non_unique,
       index_name,
       seq_in_index,
       column_name,
       index_type,
       greptime_index_type
FROM information_schema.statistics
WHERE table_schema = 'public'
  AND table_name IN ('system_metrics', 'test')
ORDER BY table_name, index_name, seq_in_index, column_name;

DROP TABLE system_metrics;

DROP TABLE test;

DROP TABLE test_no_inverted_index;