Indexes play key role in performance and scalability of any database intensive applications. Here are few tips to smartly use indexes to get the best out of it.
- Decide Best Candidates for Index. The column(s) which are most frequently used to filter records (WHERE clause and JOIN clause), group records (GROUP BY clause) and/or sort records (ORDER BY) are best candidates for index creating. Analyze your queries very attentively and avoid creating indexes which are seldom used. You should also minimize the number of indexes for the tables which has data being frequently updated/deleted/inserted to reduce index maintenance cost during each such operation.