INDEX
MySQL

Duplicate Indexes in MySQL – the Good, the Bad and the Ugly

intro

The presence of indexes aren’t news to any database administrator. The same concerns MySQL – while indexes make certain types of queries faster, using them improperly can cause major issues. We discuss one of such issues – duplicate indexes – in this blog.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

Ask any database administrator how to improve the performance of SELECT queries in your database instance and you will hear a clear answer – add some indexes to your column. Indexes are a great thing – but that’s only the case if we know how to use them properly. In any database management system, indexes are necessary if:

  1. We need to optimize SELECT queries (our DELETE and INSERT query performance will decrease.)
  2. We have a lot of data to query through.

In other cases, indexes don’t do much – but if one or both of these scenarios are true, indexes can do wonders.

How to Index?

“Indexes can do wonders.” – they say. Before you tell us we’re lying, keep in mind that such a phrase, as almost everything in the software engineering world, is relative. Indexes will only do wonders for your database if you set them up and index your database properly – that is, if you follow best practices. Some of those best practices are as follows:

  1. Choose a proper type of database index: B-tree indexes are the most frequent type of a database index, but if you’re searching for fulltext search capabilities, you may be required to use a fulltext index instead. If you want all of your column values to be unique, look into a UNIQUE index. To store rows in a descending order, use a DESCENDING index type.
  2. Manage your data well: normalize data where necessary, take a look into partitioning, sharding, data type optimization, and other things outlined in the documentation of your DBMS for the best results.
  3. Choose optimal character sets for your data: as of MySQL 8.0, character sets are now obsolete, but if you find yourself using an older version of the DBMS, certainly follow this advice. Choose latin1 if you’re storing easten European character sets, use big5 for Chinese characters, and utf8mb4 (utf8 is not the real UTF-8 in MySQL – UTF needs to support up to four bytes while the initial version of MySQL’s UTF supports only up to three) for everything else.
  4. Don’t store unnecessary data inside of your database instances: indexing data is pointless if half of it isn’t even necessary. Eliminating unnecessary rows before indexing is always a great idea.

Follow this advice and you should be on a good path – let us remind you that there will be a few “gotchas” on your way though. One of them are fulltext indexes in older MySQL versions – if you’re using MySQL 5.7 and use a fulltext index, try not to use the full-text index with a Boolean mode through big data sets that have an “@” sign in them – if you do that, you will find that your SQL queries don’t even finish and that’s for a good reason: that’s a bug within MySQL that was found by one of our staff members.

The other is a little less serious, but more frequent – duplicate indexes within your DBMS. As far as MySQL is concerned, the RDBMS doesn’t do anything to prevent us from double-indexing our data and that can be a problem if we have a lot of tables or simply are swamped with work and forget that we’ve already indexed the column in question. We’ll explain everything as we use DbVisualizer – install the SQL client, make sure to connect to your database instance by creating or editing your database connections (see photo below) then follow along.

Editing a Database Connection.
Editing a Database Connection.
Setting a Database Password.
Setting a Database Password (we‘ve reset our user password, and as a result, had to change the password here as well.)

Duplicate Indexes

For duplicate indexes to be an issue, your column needs to be indexed to begin with – if you’re reading this blog we suppose that it already is, but for the purposes of this blog, we’ll index ours:

Column Size too Large.
Column Size too Large.

Oops – we’ve got an “Index column size too large” error meaning that our columns are too large – as MySQL mentions, the maximum column size is 767 bytes wile our column size with an index on it would be larger. To solve such an error, please make your column size smaller, then run the same query again. Here we make the column hold 55 characters at best thus decreasing column size in bytes (the trick here is to only minimize the character size while leaving everything else intact):

Copy
        
1 ALTER TABLE [table_name] CHANGE [column_name] [column_name] DATATYPE(LENGTH) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin [NOT NULL] [DEFAULT ''];
Making Columns Smaller - SQL Query.
Making Columns Smaller - SQL Query.

Now, where were we? Oh, adding an index:

Copy
        
1 ALTER TABLE [table_name] ADD INDEX [idx_name]([column_name]);
ALTER Table Results.
ALTER Table Results.

Great – we have an index! Now assume we forgot we have one and add an index again:

Duplicate Index Error.
Duplicate Index Error.

Why Are Duplicate Indexes Bad?

Our index will be added, however, not without an error – DbVisualizer itself will tell you that such a practice is deprecated and disallowed in a future release. Why? That‘s because duplicate indexes:

  1. Waste hard drive space – each index serves a purpose, but it also occupies hard drive space. Now think about two indexes serving the same purpose. Will they occupy hard drive space? Yes.
  2. Make work with our database harder – multiple indexes serving the same purpose will certainly make it harder to determine which index is used for what.
  3. Are the cause of suboptimal performance – indexes are used once database decides what index to use. If there are two the same indexes on the same column, your database will get confused, and chances are that your database performance will consequentially drop.
  4. Make backups bigger – if you backup your data, chances are that you pretty much know how much hard drive space each backup is going to occupy on the disk. Double-index your database and expect bigger backups. Oh, and they also take longer to recover. Crap.
  5. Obstruct replication – depending on which replication method you choose, duplicate indexes will add to the amount of data that your database needs to replicate thus increasing replication time. Not good.
  6. Make your ibdata1 size bigger – ibdata1 is the file that holds all of the data, indexes, and MVCC data of your InnoDB-based tables. Duplicate indexes mean bloat in ibdata1 – a file that cannot be made smaller. Do you want the risk?

We could continue, but you get the point – duplicate indexes are never good. Most of them are a result of developer errors, so if you find two of the same indexes in your database, remove one of them.

Multiple Indexes

Some people may confuse duplicate indexes with multiple indexes too – we‘re mentioning this here just for you to keep in mind that they‘re separate. Multiple indexes allow you to have two indexes on the same column each serving a different purpose (e.g. you may need to have a B-tree index and a FULLTEXT index on the same column at the same time to allow for fulltext-based boolean searches), so don‘t confuse duplicate indexes with multiple indexes.

Once you make sure you haven‘t got those two confused, it‘s time to get rid of the duplicate indexes within your database and let the rest of indexes do their work.

How Do I get Rid of Duplicate Indexes?

Lucky for you, getting rid of duplicate indexes is a piece of cake. Here‘s what you have to do:

  1. Run a DESCRIBE [table_name] query and look for indexes (if you use DbVisualizer, MUL means multiple):
Multiple Indexes on a Table.
Multiple Indexes on a Table.
  1. Investigate what kind of indexes you have on that column by running a SHOW INDEXES FROM [table_name] query (you can also start from this step if you‘re in a rush):
All Indexes on a Table.
All Indexes on a Table.
  1. Drop the unnecessary index – DROP INDEX [idx_name] ON [table_name].
Dropping a Duplicate Index with DbVisualizer.
Dropping a Duplicate Index with DbVisualizer.

You got rid of a duplicate index – you should be good to go, but please be more careful next time.

Summary

Duplicate indexes are a problem within any database management system – they occupy hard drive space, obstruct replication operations, and often are the cause of suboptimal database performance.

FAQs

What Are Duplicate Indexes?

Duplicate indexes mean two indexes of the same type on the same column within a DBMS. The DBMS is not necessarily MySQL.

Why Are Duplicate Indexes Bad?

Duplicate indexes arebad because they waste hard drive space, obstruct replication operations, and are the cause of suboptimal database performance.

How to Get Rid of Duplicate Indexes?

Please follow the steps given within this blog – identify the table with a potential indexing problem, then acquire all indexes, and drop an unnecessary index.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

MySQL Binary Logs – Walkthrough

author Lukas Vileikis tags Binary Log MySQL 6 min 2024-09-18
title

MySQL SHOW TABLES Statement: What it is, How It Works, What It Means for You

author Lukas Vileikis tags MySQL 5 min 2024-09-16
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

Everything You Need to Know About MySQL Full-Text Search

author Lukas Vileikis tags Full text search MySQL 6 min 2024-08-28
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗