Does a UNIQUE constraint automatically create an INDEX on the field(s)?

Question

Should I define a separate index on the email column (for searching purposes), or is the index is "automatically" added along with UNIQ_EMAIL_USER constraint?

CREATE TABLE IF NOT EXISTS `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `first` varchar(255) NOT NULL,
  `last` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_SLUG` (`slug`),
  UNIQUE KEY `UNIQ_EMAIL_USER` (`email`,`user_id`),
  KEY `IDX_USER` (`user_id`)
) ENGINE=InnoDB;

EDIT: as suggested by Corbin i queried for EXPLAIN SELECT * FROM customer WHERE email = 'address' on empty table. This is the result, i don't know how to interpret it:

id select_type type possible_keys key  key_len ref  rows Extra
1  SIMPLE      ALL  NULL          NULL NULL    NULL 1    Using where

While adding an IXD_EMAIL to the table the same query shows:

id select_type type possible_keys key       key_len ref   rows Extra
1  SIMPLE      ref  IDX_EMAIL     IDX_EMAIL 257     const 1    Using where

A UNIQUE constraint doesn't technically require an index ... but not sure how the standard defines it or MySQL (which backend, btw?) implements it. All I can find in the MySQL manually quickly is "A UNIQUE index creates a constraint such that all values in the index must be distinct.". — , Mar 19 '12 at 02:04
You need to implement & test with individual vs covering (AKA composite - more than one column) indexes. It depends on use, and data. — OMG Ponies, Mar 19 '12 at 02:08
I'm 99% sure that it does indeed create an index. Just create a table with a unique then do an explain on a select with a where. — Corbin, Mar 19 '12 at 02:10
Did it use the unique constraint as an index? You may need to use a significantly large table by the way, or it may just do a table scan. — Corbin, Mar 19 '12 at 02:15
As an aside, in Oracle, a unique constraint will implicitly create an index with a not-too-helpful name like "SYS_...". So it can be useful to create your table first, then create a unique index with a nice name like on the pk column, then do an "alter table ... add constraint primary key ... using index table_col_uk". That way, if an error is reported (say "unique key violation on table_col_uk") you know the table and column right off the bat with out having to go to data dictionaries (as you would with a SYS_... name). But "using index" is Oracle not Mysql. — Glenn, Mar 19 '12 at 02:30

score 157 · Accepted Answer · edited Dec 16 '19 at 16:23

157

A unique key is a special case of index, acting like a regular index with added checking for uniqueness. Using SHOW INDEXES FROM customer you can see your unique keys are in fact B-tree type indexes.

A composite index on (email, user_id) is enough, you don't need a separate index on email only - MySQL can use leftmost parts of a composite index. There may be some border cases where the size of an index can slow down your queries, but you should not worry about them until you actually run into them.

As for testing index usage you should first fill your table with some data to make optimizer think it's actually worth to use that index.

edited Dec 16 '19 at 16:23

informatik01

16,038
10
74
104

answered Mar 19 '12 at 02:49

piotrm

12,038
4
31
28

So `EXPLAIN` test shows falsy values because of the empty table? – gremo Mar 19 '12 at 02:55
I'm not sure how you managed to get that explain result, I've just copied your table definition and the same explain shows UNIQ_EMAIL_USER as possible key, can you please recheck it? – piotrm Mar 19 '12 at 03:01
Ok, found the trick. When the constraint is defined using `user_id` first and then `email` it doesn't show up in `EXPLAIN`. Are you aware of this? – gremo Mar 19 '12 at 03:14
16

It doesn't work because email is not a leftmost part of (user_id,email) pair. You can't go down b-tree to find your row using only rightmost part. – piotrm Mar 19 '12 at 03:20

Does a UNIQUE constraint automatically create an INDEX on the field(s)?

1 Answers1

Linked