List unique indexes in MariaDB database

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

Check out also list of unique constraints.

Query

select index_schema,
       index_name,
       group_concat(column_name order by seq_in_index) as index_columns,
       index_type,
       table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql',
                           'performance_schema', 'sys')
                   and non_unique = 0
group by index_schema,
         index_name,
         index_type,
         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
  • table_name - name of the table

Rows

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

Sample results

Unique indexes in the Sakila database (schema):