The query below lists all indexes in the database (schema).

Query

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

Columns

• index_schema - index schema (database)
• index_name - name of the index
• index_columns - list of index columns separated by ","
• index_type
  • BTREE
  • RTREE
  • FULLTEXT
  • HASH
  • SPATIAL
• is_unique - whether index is unique
  • Unique
  • Not unique
• table_name - name of the table

Rows

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

Sample results

Indexes in the Sakila database (schema).