Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Use indexes when: high selectivity (many distinct values), frequent filters/joins, read-heavy workload. Avoid when: low cardinality (e.g., gender), small tables (full scan is cheap), write-heavy (index maintenance cost), columns rarely in WHERE. Over-indexing causes slow writes...
Use indexes when: high selectivity (many distinct values), frequent filters/joins, read-heavy workload. Avoid when: low cardinality (e.g., gender), small tables (full scan is cheap), write-heavy (index maintenance cost), columns rarely in WHERE. Over-indexing causes slow writes and storage bloat. Rule of thumb: index columns in WHERE, JOIN ON, ORDER BY. Monitor with pg_stat_user_indexes (Postgres) or sys.dm_db_index_usage_stats (SQL Server). Drop unused indexes. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.
Red Flag: Generic textbook answers. Pro-Move: 'At scale we measured X, implemented Y, achieved Z%—validated and iterated.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.