# SHOW INDEX\_STATISTICS

## Syntax

```sql
SHOW INDEX_STATISTICS
```

## Description

The [information\_schema.INDEX\_STATISTICS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-index_statistics-table) 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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-table-statistics) statement.
{% endtab %}

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

The [userstat](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#userstat) system variable must be set to 1 to activate this feature. See the [User Statistics](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries/user-statistics) and [information\_schema.INDEX\_STATISTICS](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-index_statistics-table) 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" %}