0

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';
Improve this question
6
  • 4
    What is a slug? – ypercubeᵀᴹ Mar 4 '19 at 9:30
  • Yes. you can but the field should be an index key – Dev Ramesh Mar 4 '19 at 9:45
  • @ypercubeᵀᴹ A slug is the part of a URL which identifies a particular page on a website in an easy to read form. For example, SELECT * FROM countries WHERE slug = 'new-york'; – Shahin Mar 4 '19 at 10:08
  • Should I add UNIQUE constraint to my slug column? I need to select rows with a slug, will it be helpful? No. Constraint is not relative to performance. – Akina Mar 4 '19 at 10:17
  • @Akina In MYSQL UNIQUE automatically adds INDEX to the column. By saying "Should I add UNIQUE constraint to my slug column?" I mean that should I add INDEX to my column? – Shahin Mar 4 '19 at 10:28
1

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) ;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.