Does UNIQUE effort automatically create an INDEX in a field?

Question

Should I define a separate index in the email column (for search purposes) or is the index automatically added along with the 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 requested for EXPLAIN SELECT * FROM customer WHERE email = 'address' an empty table. This is the result, I do not 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 

When IXD_EMAIL is added 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 

Answer 1

A unique key is a special case of an index, acting like a regular index with an added uniqueness check. Using the SHOW INDEXES FROM customer , you can see that your unique keys are actually B-tree type indexes.

A complex index (email, user_id) is enough, you do not need a separate index only by email - mysql can use the left parts of the composite index. There may be some borderline cases where the size of the index can slow down your queries, but you should not worry about them until you come across them.

As for testing the use of the index, you must first populate the table with some data so that the optimizer thinks that it is really worth using this index.