Optimizing MySQL: How to Spot and Eliminate Duplicate Indexes

by

When we talk about databases, indexes are a very important feature. They help us find data quickly. But if we have duplicate indexes, it can cause some problems. First, they use more space than necessary. Imagine you have two copies of the same book on your shelf; it takes up space that you can use for other books. Same with indexes. Extra indexes can take up space that your database could use for other things.

Another reason is about performance. When you add or change data, MySQL has to update all the indexes. If you have duplicate indexes, MySQL has to do extra work. It’s like if you had to write the same information in two different places every time – it’s just extra time and effort. This can make your database slower. So, having duplicate indexes can be a waste of space and can slow down your database. It’s good to avoid them.

To demonstrate how you can quickly find and remove duplicate indexes, let’s create a sample table with two pairs of indexes.

CREATE TABLE employee (
    id INT,
    name VARCHAR(20),
    start_dt DATETIME,
    is_remote TINYINT
);

INSERT INTO employee (id, name, start_dt, is_remote)
VALUES
    (1, 'John Doe', '2022-01-01 09:00:00', 1),
    (2, 'Jane Smith', '2023-03-15 10:00:00', 0);

create index nc_eployee_start_date1 on employee(start_dt);
create index nc_eployee_start_date2 on employee(start_dt);

create index nc_eployee_name_start_date1 on employee(name, start_dt);
create index nc_eployee_name_start_date2 on employee(name, start_dt);

We have 2 indexes on column start_dt and 2 indexes on columns name and start_dt with different names, but same columns.

To find duplicates you can use the following script, which uses INFORMATION_SCHEMA.STATISTICS table.

SELECT s.indexed_columns, GROUP_CONCAT(INDEX_NAME) as indexes
FROM (
    SELECT INDEX_NAME, GROUP_CONCAT(CONCAT (TABLE_NAME,'.',COLUMN_NAME) ORDER BY CONCAT (SEQ_IN_INDEX,'COLUMN_NAME')) as indexed_columns
   FROM INFORMATION_SCHEMA.STATISTICS
   GROUP BY INDEX_NAME
)as s
GROUP BY indexed_columns
HAVING COUNT(*) > 1;

You should get the following result. In the first column you’ll find the indexed columns separated by comma and on the second column you’ll see the indexes names.

Now it’s time to look more thoroughly at these indexes and remove the duplicates using the DROP INDEX column if needed.