MySQL Unique Indexes design

Question

Thank you one and all for you're answers.

I'm learning as I'm going - so I'm learning but still got a long ways to go. For work, we primarily use MySQL for our databases - web development. I have a question concerning unique indexes - when to use them properly and how.

I'm sure pretty I understand their purpose - declare a column as a "non-duplicate" column, aka no value can exist twice in the table for that specific column. If an insert/update is attempted that violates that index rule, then it fails.

I'm creating a user system for a site, and I'm designing the database tables for the system. One table is a "user" table - holds users

users
-----------------
int(4) usersID primary key
varchar(255) lastName
varchar(255) firstName
varchar(255) email unique
varchar(255) password

I have created an unique index on 'email' because each e-mail must be different for each user - it will be used as their log-in. However, I would also like to create a unique index for 'lastName', 'firstName', and 'email' because no two users should have the same combination of those three. Should I create another unique index for that rule or just create one for those three? Is it bad to repeat columns in indexes?

Answer 1

While it's not bad to have a column in multiple indexes in some instances, it's redundant for what you're trying to do.

Since you're not allowing a duplicate of the email address, no one will be able to submit a duplicate first/last/email since it will already fail the UNIQUE check on email.