List table indexes in MySQL database

The query below lists table indexes.

Query

select table_schema as database_name,
       table_name,
       index_name,
       group_concat(column_name order by seq_in_index) as columns,
       index_type,
       case non_unique
            when 1 then 'Not Unique'
            else 'Unique'
            end as is_unique
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql',
                           'performance_schema', 'sys')
group by table_schema,
         table_name,
         index_name,
         index_type,
         non_unique
order by table_schema,
         table_name;

Columns

  • database_name - name of the database (schema)
  • table_name - name of the table for which the index is defined
  • index_name - name of the index
  • index_type
    • Primary key
    • Unique
  • index_name - name of the index
  • columns - list of index columns separated by ","
  • index_type
    • BTREE
    • RTREE
    • FULLTEXT
    • HASH
    • SPATIAL
  • is_unique - whether index is unique
    • Unique
    • Not unique

Rows

  • One row: represents one index
  • Scope of rows: all indexes (unique and not unique) in the specified database (schema)
  • Ordered by: database (schema) name and table name

Sample results

0
There are no comments. Click here to write the first comment.