CODEWITHSUNDEEP
mysqlindexingunique-index
Mike Flynn
Mike Flynn

Reputation: 24325

MySQL UNIQUE and INDEX use on Column

I would like to add a index for quick lookups and uniqueness for a column called "Username". I see an option to choose UNIQUE or INDEX. Should I create both, or just go with UNIQUE?

I am not using username as a primary key guys.

Upvotes: 1

Views: 292

Answers (4)

golja
golja

Reputation: 1093

In MySQL under the hood whenever you make an attribute UNIQUE it will be INDEX-ed too. Some database designer like to explicitly define UNIQUE attributes also as INDEX, but whatever your choice the result will be the same.

Upvotes: 2

nawfal
nawfal

Reputation: 73283

UNIQUE creates an INDEX automatically for most dbs. So to answer you, you should create just one, which is UNIQUE. You can find a relevant thread here for MySQL

Upvotes: 2

Ryan
Ryan

Reputation: 14659

A primary key on a table is also automatically an index in mySql. So if you want to have a column be an index, and a unique key, then you can create a table like this:

 CREATE TABLE `users` (
    `username`,
     PRIMARY KEY (`username`),
     UNIQUE KEY `new_mmp_id` (`username`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

So in the end, you have an index on the username field that is a unique field itself. It is also the primary key, which is an index automatically. You never explained the specific fields in the table, like if you need an auto incremental id field

Should I create both, or just go with UNIQUE?

If you must have unique fields on the username column, then you should def. create both, if not then just set the username to the primary key which is an index.

I suggest your read this article http://www.xaprb.com/blog/2009/09/12/the-difference-between-a-unique-index-and-primary-key-in-mysql/

What are differences between Index v.s. Key in MySQL

Upvotes: 0

curtisdf
curtisdf

Reputation: 4210

My hunch is UNIQUE will give you the best performance boost, but INDEX would also give a boost. I think the only reason you wouldn't want to use UNIQUE is if you don't want MySQL to handle checking for unique values whenever you do an INSERT or an UPDATE.

I don't think you need both. But I'm no expert.

Upvotes: 0

Related Questions