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.
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:
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:
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.
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:
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):
1
ALTER TABLE [table_name] CHANGE [column_name] [column_name] DATATYPE(LENGTH) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin [NOT NULL] [DEFAULT ''];
Now, where were we? Oh, adding an index:
1
ALTER TABLE [table_name] ADD INDEX [idx_name]([column_name]);
Great – we have an index! Now assume we forgot we have one and add an index again:
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:
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:
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.
