Duplicate indexes when using `serial('').primaryKey()`

I have observed than whenever I use

serial('id').primaryKey()

serial('id').primaryKey()

in one of my tables, the primary key index gets duplicated.

mysql> SHOW INDEXES FROM tokens;
+--------+------------+--------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name                             | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+--------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tokens |          0 | PRIMARY                              |            1 | id            | A         |        1001 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tokens |          0 | id                                   |            1 | id            | A         |        1001 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |

mysql> SHOW INDEXES FROM tokens;
+--------+------------+--------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name                             | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+--------------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tokens |          0 | PRIMARY                              |            1 | id            | A         |        1001 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tokens |          0 | id                                   |            1 | id            | A         |        1001 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |

This does not happen for tables where the primary key is not a serial (I have one where the primary key is a varchar and there's no index duplication).

Is this expected for any reason? Am I doing something wrong?

lelabo•1/26/24, 11:15 AM

I just came to the same question myself... after digging to understand the default indexes for composite primary key and checking on a 'normal' table.

Did you find any explaination?

lelabo•1/26/24, 11:22 AM

The question I also have, which might help (or not):
Can we redefine the default indexes in the schema?

I checked the indexes for a junction table and instead of two separate indexes for each key, I am thinking of a multi index on the most important key and a simple index for the secondary one.
This way, I cover all use-cases and query using both key (hmm, many-to-many join for example) should benefits from the multi index.

Angelelz•1/27/24, 2:01 PM

I believe serial is just an alias for

Angelelz•1/27/24, 2:01 PM

So that maybe the reason, it's creating a unique index for you