You identify which indexes a query uses by asking the database's query optimizer to show its execution plan, typically with the EXPLAIN (or EXPLAIN PLAN) command. The plan reveals the access path chosen for each table, including whether an index scan, index seek, or full table scan is used.
Key Points: • EXPLAIN (MySQL/PostgreSQL) or EXPLAIN PLAN (Oracle) prints the steps the optimizer will take before or instead of actually running the query. • The "key" or "index" column in the output tells you exactly which index, if any, was chosen. • A "type" of ALL (MySQL) or a Seq Scan (PostgreSQL) signals a full table scan, meaning no useful index was used. • EXPLAIN ANALYZE (PostgreSQL) or profiling tools go further and show actual execution time and row counts, not just the estimated plan. • Database-specific system views, such as MySQL's INFORMATION_SCHEMA.STATISTICS or SQL Server's sys.dm_db_index_usage_stats, show which indexes exist and how often they are used over time.
Example: Running EXPLAIN on a query filtering by an employee's name shows whether the optimizer used an index on the name column or fell back to scanning the whole table, which immediately tells you if an index needs to be added.
Code Example:
EXPLAIN SELECT * FROM employees WHERE name = 'John';Interview Tip: A concise interview answer is:
"I run EXPLAIN, or EXPLAIN ANALYZE for actual runtime numbers, on the query to see the optimizer's execution plan. The plan's key/index column tells me exactly which index was chosen, and a full table scan in the type column tells me none was used, which points me toward adding or fixing an index."