MySQL Database and SQL: How Clustering Indexes Can Help

by Tosska Technologies sql server performance tuning- Tosska Technologies

Clustering indexes can improve update and delete performance because they can make queries faster. Although they could degrade update performance because they also have to be updated with every update, in certain situations, they actually do the opposite.

In these cases, clustering indexes can dramatically boost MySQL database and SQL performance in terms of update and delete tasks. In this article, we will examine this with the help of an example.

Before we begin, it is important to recall that updates and deletions typically include two steps. The first one is to locate all the required rows with the help of a query that contains the Where clause, whereas the second is modifying the rows that have been located. These rows may either be updated or deleted depending on the need, and the total time it takes for both these tasks is the time it takes for the query to take effect, added to the time it takes for the change to occur.

This time may grow or shrink based on the number of indexes, which means the more you add the slower the deletion, since every deleted row will require a deletion action for every index.

However, updates work somewhat differently because only the indexes that have the data the needs to be modified will be touched. On the other hand, each new clustering index will raise the time it takes for the modification to take effect because a clustering index stores all the information.

Since indexes are created to improve database speed, they are helpful in MySQL SQL performance tuning. So, if we wish to decrease the total time it takes to modify data, and adding a clustering index increases the time it takes to change data in the database, then there needs to be some compensation by significantly reducing the time it takes to execute the query.

Typically, the primary index is required for finding all it takes to perform an update or delete action. However, any clustering index is sufficient as well, and it may even be much quicker for certain update or delete tasks in comparison with the primary table. So, it can compensate the time it increases for changes to take place by decreasing the time it takes for updates and delete queries to take effect.

For instance, if we consider a table in which fiftyM rows are present with just the primary key, and we proceed to update around fifty thousand rows - only about 0.1 percent of data in MySQL database and SQL, it takes around 38.28 seconds (suppose). Since there are no indexes, this is the entire time taken to perform a complete table scan.

If we insert a key in one of the fields, the time increases multifold because it isn’t a clustering index, and the query plan’s issue comes to light in the form of a much slower time.

The issue is that an attempt is made to use the new index to fetch only the rows needing to be modified, and tries to forgo a complete scan. The index is not a clustering one though, and the database ends up making random point queries for fetching each row, which becomes all the more time consuming instead of the initial table scan.

So, what is the best way to fix it? In order for the MySQL SQL performance tuning tool to give you the best solution, it is necessary to ensure that the main table as well as the clustering index are changed with a modifying query, thereby effectively compensating the time increase with a substantial time decrease.

We cannot forget that clustering indexes are not always successful in increasing the speed of update and delete queries, especially when it comes to updating only a section of the table, as opposed to a complete table scan. What happens in such cases is, the time taken to change all the data increases but it isn’t compensated, or even leveled, by an effective time reduction in executing the insertion or update queries.

Truthfully, there are simply too many factors to point out with the help of a single example and these can actually complicate matters for the MySQL database and SQL instead of solving them at times.

Ultimately, we can say that clustering indexes are far more useful when the update or delete requirements are limited to a small part of data in the table rather than the entire table. In such situations, a clustering key can be added to decrease the time it takes for the query to be executed in the statement and, in return, decrease the time it takes to run the updates and deletions.