Drizzle Team•12mo ago

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

I have observed than whenever I use 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?

2 Replies

lelabo•9mo ago

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? 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•9mo ago

I believe serial is just an alias for bigint not null auto_increment unique So that maybe the reason, it's creating a unique index for you

Gaming

Programming

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