Mysql – Indexing VARCHAR column

indexMySQLunique-constraint

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';

Best Answer

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) ;
Related Question