Question about MySQL Indexes

[cancer10]

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.

[rcashell]

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.

[cancer10]

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

[rcashell]

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.

[cancer10]

Thanks for the explanation sir