Discuss the purpose of using indexes in a database. How do they enhance query performance, and what are the potential trade-offs associated with their use? Provide examples of scenarios where indexing is beneficial and where it may be counterproductive.
Focus on the mechanics of indexing, including types of indexes, their benefits in query optimization, and situations where they may introduce overhead. Aim for a comprehensive understanding of how indexes work and their implications on database performance.