Should I add INDEX
to my VARCHAR
column? I need to select rows with a string, not with ID, will it speed my queries?
Example:
SELECT * FROM `cities` WHERE `slug` = 'new-york';
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityShould I add INDEX
to my VARCHAR
column? I need to select rows with a string, not with ID, will it speed my queries?
Example:
SELECT * FROM `cities` WHERE `slug` = 'new-york';
You plan to have queries of the type:
SELECT * FROM cities WHERE slug = 'new-york';
So, yes, you should definitely have an index on (slug)
.
Should it be UNIQUE
index or a UNIQUE
constraint?
That depends on whether you allow to have two rows in the table with same "slug". I think this is unlikely to be a requirement, as "slugs" are usually short terms to uniquely identify a thing (user, city, country, web page, etc).
So, yes, the index should be UNIQUE
. MySQL makes no difference between UNIQUE INDEX
and UNIQUE CONSTRAINT
. I prefer to use the UNIQUE
constraint syntax (which adds a UNIQUE
index):
ALTER TABLE cities
ADD CONSTRAINT cities_slug_uq -- constraint name
UNIQUE cities_sluq_uqx -- index name
(slug) ;
countries
WHEREslug
= 'new-york';UNIQUE
automatically addsINDEX
to the column. By saying "Should I addUNIQUE
constraint to my slug column?" I mean that should I addINDEX
to my column?