> 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

View usage statistics for table indexes. This statement displays how often specific indexes are used, helping optimize query performance.

## 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.

`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.

The [userstat](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries/user-statistics.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" %}