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.
- If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity
With the most selective columns to the left most of the key.
The WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.The order of the columns in a multi column index plays a key role during query execution. The composite index used only when the left most column is included in WHERE clause of the query otherwise the index will not be hit and instead an expensive full table scan will be performed.For example, if you create composite index such as “Branch, Department”, then the query with the WHERE clause such as “WHERE Branch = ‘New York’” will use the index, but the query with the WHERE clause such as “WHERE Department = ‘Finance’” will not use the index.
- Every table should have a clustered index. Cluster index ensures that data rows stored in particular order only. If you do not create a cluster index for a table, the data rows will not be stored in any particular order, and ‘Heap’ will be used. Every time data is inserted into this table, the row will be added to the last block of the table. When many rows will be added concurrently, a “hot spot” can occur in absence of proper locking. To avoid “hot spot” and improve concurrency, you should create a clustered index for each table.
- Carefully create clustered index instead of non clustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results. However, make sure to NOT create clustered index on column(s) that is updated very frequently. Because clustered index ensures that data is stored in order of key to the index, updating of key data will cause lot of index maintenance which is not desirable for performance centric application.You should choose column(s) for clustered index very very carefully because every table can have only one clustered index. First you analyze all your queries and select optimum candidate which is best suitable for the above mentioned criteria to create the clustered index.
By smartly placing indexes you can achieve great response times to your database applications and offer better user experience.
EXPENSIVE BUSINESS DECISIONS 'PROFITABLE'.
Latest posts by Gateway Technolabs (see all)
- Kudos to Team Gateway! - June 27, 2016
- Thank You - June 26, 2015
- Gateway Technolabs steals the top spot at Nokia X Go-Port Event Organized by Nokia - April 22, 2014