All Products
Search

This Product

    PolarDB:SHOW INDEX

    Document Center

    PolarDB:SHOW INDEX

    Last Updated:Mar 28, 2026

    Use SHOW INDEX to list all indexes on a table in PolarDB-X, including local secondary indexes (LSIs) and global secondary indexes (GSIs).

    Syntax

    SHOW {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

    The keywords INDEX, INDEXES, and KEYS are interchangeable. Use FROM or IN to specify the table, and optionally the database. Add a WHERE clause to filter results by column value.

    Example

    SHOW INDEX FROM t_order;

    Output:

    +---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| TABLE   | NON_UNIQUE | KEY_NAME  | SEQ_IN_INDEX | COLUMN_NAME    | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| t_order |          0 | PRIMARY   |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order |          1 | l_i_order |            1 | order_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order |          0 | g_i_buyer |            1 | buyer_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order |          1 | g_i_buyer |            2 | id             | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            3 | order_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            4 | order_snapshot | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
6 rows in set (0.01 sec)

    The output shows three indexes on t_order: the PRIMARY key, a local secondary index l_i_order, and a global secondary index g_i_buyer. The GSI g_i_buyer spans four rows — one for its index key column (buyer_id, marked INDEX) and three for its covering columns (id, order_id, order_snapshot, each marked COVERING).

    Output columns

    ColumnDescription
    TABLEThe table name.
    NON_UNIQUEWhether the index allows duplicate values. 0 means the index is unique; 1 means it is not unique.
    KEY_NAMEThe index name.
    SEQ_IN_INDEXThe position of the column within the index, starting from 1.
    COLUMN_NAMEThe column included in the index.
    COLLATIONThe sort order for the column in the index. A = ascending, D = descending, NULL = unsorted.
    CARDINALITYThe estimated number of unique values in the index.
    SUB_PARTThe index prefix length in characters. NULL means the entire column is indexed rather than a prefix.
    PACKEDWhether the key is packed (compressed). NULL means no compression is applied.
    NULLWhether the column allows NULL values. YES means NULL is allowed; blank means it is not.
    INDEX_TYPEThe index implementation. BTREE or HASH for local indexes; GLOBAL for global secondary indexes. NULL means the type is unspecified.
    COMMENTThe role of the column in a global secondary index. Blank indicates a local secondary index. For GSIs, INDEX marks an index key column and COVERING marks a covering column.
    INDEX_COMMENTAdditional comments about the index, specified when the index was created.
    The COMMENT column is PolarDB-X-specific. Standard MySQL always returns a blank value here. Use COMMENT to distinguish LSIs from GSIs and to identify which columns are index key columns versus covering columns within a GSI.

    What's next