Information Schema STATISTICS Table

The Information Schema STATISTICS table provides information about table indexes.

It contains the following columns:

Column
Description

Column

Description

TABLE_CATALOG

Always def.

TABLE_SCHEMA

Database name.

TABLE_NAME

Table name.

NON_UNIQUE

1 if the index can have duplicates, 0 if not.

INDEX_SCHEMA

Database name.

INDEX_NAME

Index name. The primary key is always named PRIMARY.

SEQ_IN_INDEX

The column sequence number, starting at 1.

COLUMN_NAME

Column name.

COLLATION

A for sorted in ascending order, or NULL for unsorted.

CARDINALITY

Estimate of the number of unique values stored in the index based on statistics stored as integers. Higher cardinalities usually mean a greater chance of the index being used in a join. Updated by the ANALYZE TABLE statement or myisamchk -a.

SUB_PART

NULL if the whole column is indexed, or the number of indexed characters if partly indexed.

PACKED

NULL if not packed, otherwise how the index is packed.

NULLABLE

YES if the column may contain NULLs, empty string if not.

INDEX_TYPE

Index type, one of BTREE, RTREE, HASH or FULLTEXT. See Storage Engine Index Types.

COMMENT

Index comments from the CREATE INDEX statement.

IGNORED

Whether or not an index will be ignored by the optimizer. See Ignored Indexes. From MariaDB 10.6.0.

The SHOW INDEX statement produces similar output.

Example

SELECT * FROM INFORMATION_SCHEMA.STATISTICS\G
...
*************************** 85. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: test
   TABLE_NAME: table1
   NON_UNIQUE: 1
 INDEX_SCHEMA: test
   INDEX_NAME: col2
 SEQ_IN_INDEX: 1
  COLUMN_NAME: col2
    COLLATION: A
  CARDINALITY: 6
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: 
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT:
...

This page is licensed: CC BY-SA / Gnu FDL

Last updated

Was this helpful?