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@example.com'
Will the index work in this case? (notice the unique index assigned)
Thanks for any inputs.
Yes it will use the unique index on email. If you are not sure you can always use EXPLAIN SELECT * FROM employee WHERE firstname.lastname@example.org' to see how this is being accessed.
What if I have used the INDEX key instead of UNIQUE, would it still work?
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.
Thanks for the explanation sir
This topic is now archived. It is frozen and cannot be changed in any way.