In MYSQL, what does it mean when there are duplicate indices where everything but key_name is the same?

Question

describe etc_category_metadata;

+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | int(11)       | NO   | PRI | NULL    | auto_increment |
| user_id             | bigint(20)    | NO   |     | NULL    |                |
| time_updated        | int(11)       | YES  |     | NULL    |                |
| category_type       | int(11)       | YES  | MUL | NULL    |                |
| status_keywords     | mediumblob    | YES  |     | NULL    |                |
| page_keywords       | mediumblob    | YES  |     | NULL    |                |
| profession_keywords | mediumblob    | YES  |     | NULL    |                |
| adgroup_ids         | mediumblob    | YES  |     | NULL    |                |
| prod                | tinyint(1)    | YES  |     | 0       |                |
| version             | int(11)       | YES  |     | 1       |                |
| status              | int(11)       | YES  |     | 0       |                |
| dep_category_ids    | mediumblob    | YES  |     | NULL    |                |
| custom_param        | mediumblob    | YES  |     | NULL    |                |
| queue_priority      | int(11)       | YES  |     | 1       |                |
| auto_requeue_num    | int(11)       | YES  |     | 0       |                |
| cloned_version      | int(11)       | YES  |     | 0       |                |
| custom_query        | varchar(1000) | YES  |     | NULL    |                |
| description         | varchar(1000) | YES  |     | NULL    |                |
| error_message       | mediumblob    | YES  |     | NULL    |                |
| time_last_completed | int(11)       | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+
21 rows in set (0.40 sec)

show index from etc_category_metadata;
+-----------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                 | Non_unique | Key_name        | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| etc_category_metadata |          0 | PRIMARY         |            1 | id            | A         |       12613 |     NULL | NULL   |      | BTREE      |         |               |
| etc_category_metadata |          0 | category_type   |            1 | category_type | A         |       12613 |     NULL | NULL   | YES  | BTREE      |         |               |
| etc_category_metadata |          0 | category_type   |            2 | version       | A         |       12613 |     NULL | NULL   | YES  | BTREE      |         |               |
| etc_category_metadata |          0 | category_type_2 |            1 | category_type | A         |       12613 |     NULL | NULL   | YES  | BTREE      |         |               |
| etc_category_metadata |          0 | category_type_2 |            2 | version       | A         |       12613 |     NULL | NULL   | YES  | BTREE      |         |               |
| etc_category_metadata |          0 | category_type_3 |            1 | category_type | A         |       12613 |     NULL | NULL   | YES  | BTREE      |         |               |
| etc_category_metadata |          0 | category_type_3 |            2 | version       | A         |       12613 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------------------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.07 sec)

Trying to figure out whether it's safe to delete the keys category_type_2 and category_type_3. It seems like they are the exact same indices as category_type. This is a legacy table that I have no idea who created a long time ago. Are there any valid reasons someone would end up creating these three keys that seem duplicates?

Ordure

Answer 1

Having two identical indexes is a waste of disk space and slows down INSERTs (a little). There is no benefit.

You can't really see if they are duplicates from DESCRIBE TABLE. Instead, do SHOW CREATE TABLE. Watch for UNIQUE/not, prefixed/not, regular/FULLTEXT, created manually / created by FOREIGN KEY, etc.

Once you decide that they are identical (except for name), do drop one. There are other cases where indexes may as well be dropped. Suppose you had these 3 indexes:

INDEX(a,b)  -- keep this
INDEX(a)    -- unnecessary
INDEX(b)    -- keep

Or this pair:

UNIQUE(a)   -- keep; same as INDEX(a), plus a uniqueness check
INDEX(a)    -- drop

More subtle, consider this pair:

INDEX(a,b)  -- keep; provides composite index
UNIQUE(a)   -- keep; provides uniqueness check

(There are more combinations.)

Aleut

Answer 2

Yes,you have duplicate indexes on the same column so mysql just add a number if you havent given the indexes a name.IMO,mysql shoudnt even allow duplicate indexes.It is safe to delete them

DROP INDEX category_type_2  ON etc_category_metadata

and do the same for the others

Gonadotropin

Answer 3

There's no good reason to have multiple indexes on the same columns with the same order. Issues such a create index statement on a current MySQL database will still succeed (for backwards compatibility reasons), but will issue a warning:

Duplicate index 'index_name' defined on the table 'db.table_name'. This is deprecated and will be disallowed in a future release.

If you have such pre-existing duplicate indexes there's no reason not to remove them.

Giantess