Answer
Use highly selective, narrow, composite indexes. Avoid indexing low-cardinality columns and review real query patterns regularly.
Quick Answer
Index design for OLTP: identify the most common query patterns and their WHERE, JOIN, and ORDER BY columns. Create indexes for each query pattern. Composite indexes for multi-column filters. Covering indexes for high-frequency queries. Avoid redundant indexes (index on (a,b) makes a separate index on (a) redundant). Use pt-duplicate-key-checker to find redundant indexes. Monitor with sys.schema_unused_indexes.
S
SugharaIQ Editorial Team
Verified Answer
This answer has been peer-reviewed by industry experts holding senior engineering roles to ensure technical accuracy and relevance for modern interview standards.
Want to bookmark, take notes, or join discussions?
Sign in to access all features and personalize your learning experience.
Source: SugharaIQ