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.
-
I would assume that UNIQUE doesnt create an index like INDEX would for quick lookups.Mike Flynn– Mike Flynn2012-07-06 16:00:05 +00:00Commented Jul 6, 2012 at 16:00
-
You would assume incorrectly.ceejayoz– ceejayoz2012-07-07 02:53:02 +00:00Commented Jul 7, 2012 at 2:53
Add a comment
|
4 Answers
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
Comments
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.
2 Comments
nawfal
A unique makes it both, but indexing wont make it unique. So both the results wont be the same.
golja
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 ... tnxMy 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.
Comments
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/
4 Comments
nawfal
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.
golja
Also I believe if you make an attribute a
PRIMARY KEY it's UNIQUE by default.
Ryan
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
Mike Flynn
Who said Im using the username as a primary key?