CODEWITHSUNDEEP
sqlmysqlindexing
Joel
Joel

Reputation: 30146

MySql - Is an index on a column redundant if it is defined as unique?

If a table is defined to have a unique constraint on a column, is it also necessary to define a separate index on that column if we want it to be indexed for fast lookup?

For example, given the following table indexes:

mysql> show index from FRONTIER;
+----------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+-------------+---------+
| Table    | Non_unique | Key_name                | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| FRONTIER |          0 | PRIMARY                 |            1 | ID           | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| FRONTIER |          0 | uniq_cnstr              |            1 | HOST_ID      | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| FRONTIER |          0 | uniq_cnstr              |            2 | HASH_PATHQRY | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| FRONTIER |          1 | I_FRONTIER_HASH_PATHQRY |            1 | HASH_PATHQRY | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+-------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

Is the index 'I_FRONTIER_HASH_PATHQRY' redundant given unique constraint 'uniq_constr' ?

(note that the unique constraint spans 2 columns)

Upvotes: 2

Views: 374

Answers (2)

Quassnoi
Quassnoi

Reputation: 425321

A unique index is a plain index as well, there is no need to create a separate non-unique one.

However, your unique constraint covers two columns (host_id, hash_pathqry), the hash_pathqry being trailing.

An index maintains the lexicographical order of columns, so hash_pathqry are only ordered within each single value of host_id.

So the unique index you already have will not improve the lookups on HASH_PATHQRY. The I_FRONTIER_HASH_PATHQRY is still required.

Upvotes: 4

Jon Black
Jon Black

Reputation: 16559

no - if you specify unique i.e username varbinary(32) unique not null, then MySQL adds a unique index automatically.

Upvotes: 0

Related Questions