In last week's blog, we learned about the ramifications of poor indexing, as well as how to choose which columns to include as part of a clustered index. In this article, we'll cover how the same indexes that provide better performance for some operations, can add overhead for others.
How Clustered Indexes Affect INSERTs, UPDATEs and DELETEs
In general, having indexes on tables comes with the additional cost that more data pages and memory is used. On Clustered tables, the effects of indexes are even more pronounced. A Clustered table is one where a clustered index is used to store the data rows sorted based on the clustered index key values. SELECT statements tend to execute noticeably faster on a clustered table, whereas INSERTs, UPDATEs, and DELETEs require more time, as not only data is updated, but the indexes are updated also. For clustered indexes, the time increase is more significant than on single indexes, as the records have to maintain the correct order in data pages. Whether a new record is inserted, or an existing deleted or updated, this usually requires the records to be reordered.
INSERTs tend to perform fastest on a table without any indexes. This is because neither re-ordering nor index updating are required. On the same table, executing UPDATEs and DELETEs is the most expensive. The reason is that the database requires most time to find the specific records within the table.
Conversely, the costs can be higher for a table with a non-optimal clustered index, followed by on tables with a non-clustered index or no indexes at all.
With regards to SELECT statements, you can reduce the cost of execution by:
- specifying the list of returned columns, and
- executing the statement on the table where the clustered index is created on the primary key column
Examples of DML Impact
We can see the impact of indexes on DML (Data Manipulation Language) statements on the following album table, whose definition shows a large number of indexes:
In Navicat, we can view index details in the Table Designer' Indexes tab:
Both the Index Type and method may be chosen via drop-downs, which are tailored specifically to the database type. Here are the available selections for MySQL 7:
By running a simple benchmark we can test the insert rate of the current album table with the original definition that includes a Primary Index only:
Here are the timed results:
Inserting data into the table with additional indexes was four times slower in my informal simple bulk tests. There are other factors that can contribute to the slower speed; however, my results provide a representative indication that adding indexes to a table has a direct effect on write performance.
Conclusion
As shown, indexes can speed up some queries and slow down others. In this article, we provided some basic guidelines for clustered and non-clustered indexes, as well as which columns are preferred to build indexes on, and which should be avoided. Finding the right balance between the benefits and overhead indexes bring provides optimal performance to your queries and stored procedures.
Interested in Navicat Premium? You can try it for free for 14 days!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
