Understanding Duplicate Indexes on the Same Column in MySQL and Oracle

A colleague noticed a MySQL test database where a column that had already been defined as a primary key was also given an additional index, and another case where the same column received two separate indexes. The article presents these two situations.

Scenario 1 adds a primary key and then an index on the same column; Scenario 2 adds two indexes on the same column. The relevant SQL statements are:

# 情况 1
ALTER TABLE test ADD PRIMARY KEY USING BTREE(ID);
ALTER TABLE test ADD INDEX idx_test01 USING BTREE(ID);

# 情况 2
ALTER TABLE test ADD INDEX idx_test02 USING BTREE(UPDATED);
ALTER TABLE test ADD INDEX idx_test03 USING BTREE(UPDATED);

The author asks whether a single statement would normally be sufficient and notes that the situation seems meaningless.

Test 1 shows that MySQL 8.0 allows adding a primary key and then a regular index on the same column without error. Example commands:

alter table t add primary key using btree(id);
alter table t add index idx_t_id using btree(id);

Similarly, creating two identical indexes on column c1 succeeds:

alter table tbl add index idx_t_001 using btree(c1);
alter table tbl add index idx_t_002 using btree(c1);

These experiments confirm that MySQL can create multiple identical indexes on the same field.

Test 2 uses EXPLAIN to show that when both a PRIMARY KEY and a regular index exist on a column, the optimizer chooses the PRIMARY KEY as the key. When two identical indexes exist, the optimizer picks the one that was created first.

bisal@mysqldb 13:02: [test]> explain select * from tbl where id=1;
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl   | NULL       | const | PRIMARY,idx_t_id| PRIMARY | 4       | const |   1  | 100.00   | NULL  |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------+

bisal@mysqldb 13:03: [test]> explain select * from tbl where c1='a';
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl   | NULL       | ref  | idx_t_001,idx_t_002 | idx_t_001 | 7       | const |   3  | 100.00   | NULL |
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+

Test 3 demonstrates that the two identical indexes are redundant; keeping one is sufficient because they have the same columns and only differ in name.

ALTER TABLE test ADD INDEX idx_test02 USING BTREE(UPDATED);
ALTER TABLE test ADD INDEX idx_test03 USING BTREE(UPDATED);

The article explains that while a primary key includes an index, a separate non‑unique index on the same column is unnecessary and can be dropped.

Comparing with Oracle 19c, the author notes that Oracle does not allow creating a second index on a column that already has an index, nor does it permit adding an index that duplicates the primary‑key column, raising an "column list already indexed" error.

Conclusion : Different databases adopt different design philosophies; Oracle enforces stricter uniqueness, whereas MySQL is more tolerant of duplicate indexes. Understanding these fundamentals helps practitioners choose appropriate indexing strategies for their specific scenarios.