1

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.

Mike Flynn
  • 22,342
  • 54
  • 182
  • 341

4 Answers4

2

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.

golja
  • 1,063
  • 7
  • 11
  • A unique makes it both, but indexing wont make it unique. So both the results wont be the same. – nawfal Jul 07 '12 at 03:39
  • Yeah you are right, but he asked what is the best thing to do for an unique element so I assume you will need to use `UNIQUE` either way, but if he want to add `INDEX` is his choice. I will rephrase my answer to be even more clear ... tnx – golja Jul 07 '12 at 03:52
2

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

Community
  • 1
  • 1
nawfal
  • 70,104
  • 56
  • 326
  • 368
0

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

Community
  • 1
  • 1
Ryan
  • 14,392
  • 8
  • 62
  • 102
  • you are explaining to him what UNIQUE does and nothing on indexing (prior to your edit). The OP was asking which to choose in his case which u din answer. Moreover, even after edit, I feel a link which talks about differences between indexes and keys is not a proper answer. – nawfal Jul 06 '12 at 00:18
  • Also I believe if you make an attribute a `PRIMARY KEY` it's `UNIQUE` by default. – golja Jul 06 '12 at 01:00
  • I usuallly use UNIQUE key in order to also have an auto incremental ID field on a table. It means that the ID field is the primary key, but also the username field is unique so no other rows can have a combination of an existing id field and username field – Ryan Jul 06 '12 at 01:03
  • Who said Im using the username as a primary key? – Mike Flynn Jul 06 '12 at 15:58
0

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.

curtisdf
  • 4,130
  • 4
  • 33
  • 42