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,
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
id. Similarly, when we add an index on multiple columns, we're actually adding an index on
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
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
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.