SHOW INDEX 문

설명

SHOW INDEX 문은 인덱스 정보를 출력한다. 해당 질의는 다음과 같은 컬럼을 가진다.

구문

SHOW {INDEX | INDEXES | KEYS } {FROM | IN} tbl_name

예제

다음은 demodb에 대해서 해당 질의를 실행한 결과이다.

SHOW INDEX IN athlete;

   Table     Non_unique   Key_name       Seq_in_index  Column_name    Collation     Cardinality   Sub_part  Packed   Null   Index_type

==========================================================================================================================================

 'athlete'     0      'pk_athlete_code'     1          'code'           'A'           6677         NULL     NULL    'NO'      'BTREE'

 

CREATE TABLE t1( i1 INTEGER , i2 INTEGER NOT NULL, i3 INTEGER UNIQUE, s1 VARCHAR(10), s2 VARCHAR(10), s3 VARCHAR(10) UNIQUE);

 

CREATE INDEX i_t1_i1 ON t1(i1 desc);

CREATE INDEX i_t1_s1 ON t1(s1(7));

CREATE INDEX i_t1_i1_s1 ON t1(i1,s1);

CREATE UNIQUE INDEX i_t1_i2_s2 ON t1(i2,s2);

 

SHOW INDEXES FROM t1;

  Table  Non_unique  Key_name          Seq_in_index  Column_name   Collation   Cardinality     Sub_part    Packed   Null    Index_type

==========================================================================================================================================

  't1'           0  'i_t1_i2_s2'              1      'i2'          'A'            0               NULL        NULL     'NO'    'BTREE'

  't1'           0  'i_t1_i2_s2'              2      's2'          'A'            0               NULL        NULL     'YES'   'BTREE'

  't1'           0  'u_t1_i3'                 1      'i3'          'A'            0               NULL        NULL     'YES'   'BTREE'

  't1'           0  'u_t1_s3'                 1      's3'          'A'            0               NULL        NULL     'YES'   'BTREE'

  't1'           1  'i_t1_i1'                 1      'i1'          NULL           0               NULL        NULL     'YES'   'BTREE'

  't1'           1  'i_t1_i1_s1'              1      'i1'          'A'            0               NULL        NULL     'YES'   'BTREE'

  't1'           1  'i_t1_i1_s1'              2      's1'          'A'            0               NULL        NULL     'YES'   'BTREE'

  't1'           1  'i_t1_s1'                 1      's1'          'A'            0                  7        NULL     'YES'   'BTREE'