Hi, can you help me please to enlighten my mind what is the difference of this two “UNIQUE” and “INDEX” in MySQL,can you please give some little example so that i can understand thoroughly.and also when to use this “UNIQUE” and “INDEX” in my tables.
Thank you in advance 
I’m sure Rudy will shoot me down if my answer is incomplete, but essentially…
index is simply a means to look up specific rows on a table. Using an index saves the database from having to search each and every row individually. Think of it like an index in the back of a text book. You can use the index at the back of the book to look for a specific topic and find it easily without having to page each and every page to find it.
unique is used when you want to have a value exist exactly once in a column. It allows you to identify individual rows on a table.
If you’re of a type that documentation makes sense to you, you can refer to the mySQL page specifically…http://dev.mysql.com/doc/refman/5.0/en/create-index.html
Hi Jem,
Take a look at the above link and do an advanced search for Jem.
A popup box should appear showing numerous members names beginning with Jem. You will notice that there is an alphabetical INDEX on the members names but only one UNIQUE (oxymoron occurrence of a member’s name.
na, man, nice job
confusion might arise from the fact that a unique constraint is actually implemented as a unique index – so if you declare a column as UNIQUE, you do ~not~ need an additional index on it
True…if something is defined is unique, it’s an index by default. But an index does not have to be unique.
HI ,I have question but if we have primary key,…I know primary key is unique, so i don’t need anymore to add “unique index” on my table?..sorry i am confuse on this…please correct me if i am wrong…
Correct, unless there is a non-identifying field that you would for some off reason want one and only one value of.
An example would be a table of users. The primary key would identify each individual record. Now, if for some odd reason, your customer decides they only want one person from each town, you would put a unique index on the town field. From a semantic perspective, you don’t want to make this the primary key because the table is dealing with users, so you declare an extra unique index.
definitely not on the same column(s)
but you can also declare a unique index on a different column
for example, a table of users which has an auto_increment primary key, but you also want to ensure that no two users have the same name, so you declare a unique index on the name
yes, you could use the name as the primary key, but because users are referenced in many many tables (e.g. last_updated_by columns), you would prefer to use the 4-byte integer as the foreign key
For completeness sake I’d like to add that it possible to add an index or a unique index to multiple columns at once. So if you want to allow multiple users with the same username but only if they have different email addresses for example you can add a unique index to (username, email). That way the combination of the username and email have to be unique, not the columns themselves.