Index Tuning Advisor

Index Tuning Advisor is a SQL statement in the Indexes category. Tools and queries to identify missing, unused, or duplicate indexes for optimization. The syntax is -- SQL Server DTA / PostgreSQL pg_qualstats / MySQL sys.schema_unused_indexes. It returns index recommendations. A typical example: -- SQL Server: Missing index DMV SELECT * FROM sys.dm_db_missing_index_details; -- Find unused indexes (wasteful): SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, ius.user_seeks + ius.user_scans + ius.user_lookups AS usage_count FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 ORDER BY usage_count; -- MySQL: schema_unused_indexes SELECT * FROM sys.schema_unused_indexes; -- PostgreSQL: pg_stat_user_indexes SELECT idx.relname AS index, tab.relname… A close relative is CREATE INDEX, which creates an index on one or more columns to speed up query performance. A close relative is DROP INDEX, which removes an existing index from a table.