About every second application I look at has some tables which have redundant or duplicate indexes so its the time to speak about these a bit.
So what is duplicate index ? This is when table has multiple indexes defined on the same columns. Sometimes it is indexes with different names, sometimes it is different keywords used to define the index. For example it is quite frequite to see something like
PRIMARY KEY(id), UNIQUE KEY id(id), KEY id2(id)
The logic I heard behind this often – create primary key as object identifier, now we create UNIQUE because we want it to be UNIQUE and we create KEY so it it can be used in the queries. This is wrong and hurts MySQL Performance. It is enough to create PRIMARY KEY and it will enforce unique values and will be used in the queries.
The other case is simply having multiple keys on same column(s) – I guess someone thought key would make sense while did not notice it was already created. MySQL is very permissive and allows you to create many keys on the same column… furthermore these would be real separate keys inside of storage engine which take space on the disk and in memory and which need to be updated on update/insert delete. Duplicate keys are bad so once you find them get rid of them.
Note: Duplicate indexes apply to indexes of the same time. It may make sense to have indexes of different types to created on the same column(s) – perfect example is BTREE index and FULLTEXT index, while other combinations may also make sense.
Note: Order of columns in index is significant, index (A,B) is not duplicate to index (B,A)
So now what are Redundant indexes when ?
I call redundant indexes BTREE indexes which are prefix of other index, for example KEY(A), KEY (A,B), KEY(A(10)). – First and last are redundant indexes because they are prefix of KEY(A,B)
Do redundant indexes have right to exist ? In most cases it is good to get rid of them as well. Queries which take advantage of
redundant index will also be able to use longer index.
Unlike with duplicate indexes, there are however cases when redundant indexes are helpful – typically if longer index is just too long, for example if A is int and B is varchar(255) which holds a lot of long values using KEY(A) might be much faster than using KEY(A,B). So unlike in case of duplicate indexes it is good to give a good thought before removing them.
Typical case when leaving short index AND adding longer one could be when you want certain query to run as index covered query (retrieve all columns from the index) – such indexes may become way too long to be efficiently used by other queries.
There is currently no tool in MySQL distribution which will help you to check your schema for redundant and duplicate indexes. If you know one let me know. It should be also pretty simple to write.
