> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index-statistics.md).

# SHOW INDEX\_STATISTICS

## Syntax

```bnf
SHOW INDEX_STATISTICS
```

## Description

The [information\_schema.INDEX\_STATISTICS](/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-index_statistics-table.md) table shows statistics on index usage and makes it possible to do such things as locating unused indexes and generating the commands to remove them.

{% tabs %}
{% tab title="Current" %}
`SHOW INDEX_STATISTICS` is replaced by the generic [SHOW TABLE STATISTICS](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-table-statistics.md) statement.
{% endtab %}

{% tab title="< 10.1.1 / 5.3" %}
The `SHOW INDEX_STATISTICS` statement was introduced in [MariaDB 5.2](/docs/release-notes/community-server/old-releases/5.2/changes-improvements-in-mariadb-5-2.md) as part of the [User Statistics](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries/user-statistics.md) feature. It was removed as a separate statement in [MariaDB 10.1.1](/docs/release-notes/community-server/old-releases/10.1/10.1.1.md), but effectively replaced by the generic [SHOW TABLE STATISTICS](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-table-statistics.md) statement.
{% endtab %}
{% endtabs %}

The [userstat](/docs/server/server-management/variables-and-modes/server-system-variables.md#userstat) system variable must be set to 1 to activate this feature. See the [User Statistics](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries/user-statistics.md) and [information\_schema.INDEX\_STATISTICS](/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-index_statistics-table.md) table for more information.

## Example

```sql
SHOW INDEX_STATISTICS;
+--------------+-------------------+------------+-----------+
| Table_schema | Table_name        | Index_name | Rows_read |
+--------------+-------------------+------------+-----------+
| test         | employees_example | PRIMARY    |         1 |
+--------------+-------------------+------------+-----------+
```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}