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?
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.)