CODEWITHSUNDEEP
mysql
kYuZz
kYuZz

Reputation: 1732

If I have a UNIQUE index on a column, do I need to index it again to optimize search queries?

I have this table

CREATE TABLE `country` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to add a unique index so that the table cannot contain duplicate countries.

Edit: To clarify, I want the name column to have unique values, but I also want to optimize it for queries like SELECT name FROM country WHERE name = ?.

If my table were to allow duplicate countries, I'd normally achieve this with an INDEX key..

Do I need two indices on the name column (one INDEX and one UNIQUE) or will a single UNIQUE index do the job?

Upvotes: 1

Views: 57

Answers (1)

spencer7593
spencer7593

Reputation: 108400

If you want the database to enforce that the column name contains UNIQUE values, you'd need a UNIQUE KEY in place of KEY.

If you want the database to allow duplicate values for name, then the KEY you have is just fine.

Upvotes: 1

Related Questions