Question about MySQL Indexes

Databases
1

Hi

So if I have a table with a structure as below:


CREATE TABLE `employee` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `full_name` INT(64) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `mobile` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1

and I run a query


SELECT * FROM employee WHERE email='abc@example.com'

Will the index work in this case? (notice the unique index assigned)

Thanks for any inputs.

2

Yes it will use the unique index on email. If you are not sure you can always use EXPLAIN SELECT * FROM employee WHERE email=‘abc@example.com’ to see how this is being accessed.

3

What if I have used the INDEX key instead of UNIQUE, would it still work?

4

Yes, the only difference between a regular index and a unique index is that the unique index adds a unique constraint on this column. A regular index performs in exactly the same way but without the unique constraint. So you can have many logins using the same email.

5

Thanks for the explanation sir