As with everything, there are trade-offs when it comes to indexes. That said, this discussion focuses on query optimization by looking at table indexes or the lack thereof. A sign of a good database performance tuning and optimization solution is that it will analyze every query per database instance, look at all the tables used with each query, and then make a recommendation for table tuning if there’s a positive impact.
It’s important to not only see the impact on a specific query but all the queries accessing the table. I’ve seen simple table index recommendations improve query performance by 100% for a specific query but also boost performance for dozens of queries accessing the same table, resulting in as little as a 14% enhancement to as much as a triple-digit improvement. With that, let’s look at the top five recommendations.
Overlapping Indexes
Overlapping indexes have the same leading-edge column (the first column defined). Because every index has a maintenance cost that consumes disk space, identifying and removing unneeded indexes can improve performance. Examine the overlapping indexes to determine if any can be removed. For example, if two indexes include the same columns in the same order, but one includes additional columns, the smaller index is redundant and can be removed. Or, if two indexes include the same columns but each has one additional column, modify one index to include all columns and remove the other index.
Wide Index
A wide index meets at least one of the following criteria:
- The index includes five or more columns.
- The index is more than 200 bytes.
Large indexes require more storage and increase the cost of index maintenance. If the index includes five or more columns because it is a covering index for multiple queries, the performance improvement might offset the additional overhead. However, if the index is not a covering index, the cost of maintaining the index could offset any performance improvement the index provides. In this case, consider removing trailing edge columns.
Table Has No Defined Indexes
In this case, the table being queried has no defined indexes. In a situation in which the table is small, an index might not be more efficient than a full table scan. However, for larger tables, consider adding an index.
Table Has No Primary Key
A primary key provides a way to uniquely identify a record and is necessary to ensure data integrity. A primary key is also required to join a table. If no column or combination of columns provides a unique value, you can add an artificial primary key such as an ID column.
Foreign Key Is Not Indexed
A foreign key in one table (the child table) refers to the primary key of another table (the parent table). Indexing each foreign key can improve the performance of queries joining the two tables. In addition, when a foreign key is not indexed, the database must perform a full table scan of the child table whenever a row is deleted or the primary key value is updated in the parent table.
Following Indexing Best Practices
While these top five index-tuning recommendations will provide performance benefits for most queries, there are trade-offs when making index modifications. One is the cost of index maintenance increasing as the amount of data churn increases. For example, when a table row is inserted, deleted, or updated, the index will correspondingly be updated, which in turn would increase the time the insert/delete/update takes to run. In addition, the larger the table and more columns in the index, the more disk storage space it needs. Still, the cost of storage continues to go down and is generally not considered a hindrance to implementing indexing best practices.