Understanding duplicate indexes in MySQL
As a MySQL user, you likely understand the importance of creating indexes on your database tables to make queries run more efficiently. However, not all indexes are created equal, and some may end up being redundant. In this video, we'll take a deep dive into duplicate indexes in MySQL and how they can impact the performance of your queries.
Identifying duplicate indexes
To start, let's take a look at an example of duplicate indexes. Suppose we have a table called people, and we create two indexes:
ALTER TABLE people ADD INDEX first_name (first_name);
ALTER TABLE people ADD INDEX full_name (first_name, last_name, birthday);
If we run SHOW INDEXES FROM people, we'll see that we now have two indexes, first_name and full_name.
Based on what we've learned so far about composite indexes, we know that the full_name index covers the first_name index, since the first key part of the full_name index is first_name. Therefore, the first_name index is redundant and can be safely removed.
However, it's important to note that when we add an index on a column in InnoDB, we're really adding an index on column_name and id. Similarly, when we add an index on multiple columns, we're actually adding an index on column_1_name, column_2_name, column_3_name, and id. This is because InnoDB always appends the primary key to the leaf nodes of each index.
Handling redundant indexes
To remove a redundant index, we can use the following code:
ALTER TABLE people ALTER INDEX first_name INVISIBLE;
This makes the index invisible to the MySQL query planner, effectively removing it without deleting it. Now, when we run a query, MySQL will happily use the full_name index and won't even consider the first_name index.
However, it's important to note that removing a redundant index can have unintended consequences, especially if you depend on the ordering of the rows in that index. For example, if you run a query like this:
SELECT * FROM people WHERE first_name = 'Aaron' ORDER BY id DESC;
MySQL will use the full_name index for the access pattern, but it will have to manually sort the rows because the id column is all the way at the end of the index. This can have a negative impact on performance, especially for large tables.
Preventing duplicate indexes
To prevent duplicate indexes from occurring in the first place, it's important to keep an eye out for indexes that have overlapping leftmost prefixes. In our example above, we had a single column index on first_name and a composite index on first_name, last_name, and birthday. Since the first_name column is the leftmost part of both indexes, they were redundant.
If you find that you have multiple indexes with overlapping prefixes, consider removing the redundant ones to improve the performance of your queries.
