How to understand if a request uses an index? How to monitor unused indexes?

To understand whether a query uses an index, the following methods are usually applied:

1. EXPLAIN plan of the query — most DBMSs (e.g., MySQL, PostgreSQL, Oracle) support the EXPLAIN command, which shows how the DBMS plans to execute the query, including index usage.

2. Query execution monitoring — in some DBMSs, profiling or tracing can be enabled to see which indexes are involved.

3. Index usage statistics — many DBMSs keep statistics on index usage, for example, PostgreSQL has pg_stat_user_indexes, MySQL has information_schema.STATISTICS and performance_schema.table_io_waits_summary_by_index_usage.

For monitoring unused indexes:

• In PostgreSQL, you can query pg_stat_user_indexes and compare the number of index scans with the creation time.
• In MySQL, performance_schema can be used to analyze index usage.

Example of an EXPLAIN query in PostgreSQL:

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

If the plan includes Index Scan, then the index is used.

In Java applications working with the database, SQL queries can be logged and their plans analyzed through DBMS tools or ORM (for example, Hibernate supports query plan output).