varchar(255) field or hash field? To consider a table with millions records and the table schema:

CREATE TABLE `foos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `foo` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `foo_UNIQUE` (`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


Ton of this query executes every second

SELECT 1 FROM foos WHERE foo=?


If no record finds, this query will be executed:

INSERT INTO foos(foo) VALUES(?)


The average length of foo less than 20. To optimize performance, a hashing field is considered to add with md5(foo) and drop the unique key. 

ALTER TABLE foos DROP INDEX foo_UNIQUE;
ALTER TABLE foos ADD INDEX `foo_IDX` (`hash`);


And the query will be changed as:

SELECT 1 FROM foos WHERE hash=?


If no record finds, this query will be executed:

INSERT INTO foos(foo, hash) VALUES(?, ?)


The Question is:  Will the SELECT query run faster, since average length of foo less than 20 but hashing field length is always 32?
The answer is NO.  Today, Jehad's answer is wrong.  It may have been correct for some older version of MySQL and for some particular Engine (such as MEMORY before Version 8.0).

Today, Data and indexes are packed VARCHAR, they do not use the max length.  I verified with 5.6 with InnoDB and MyISAM.

That is, a VARCHAR column that averages 20 bytes will take only that much space.  However, if you experiment with this in InnoDB, the numbers will be quite confusing.  Here are some reasons why the sizes you get will be more than (20 * num-rows) in InnoDB:


Overhead for each row (in data and in index): 20-30 bytes
Overhead for each block: 10-20%
Overhead for BTree block splits: 40%.  This may not be needed if you insert rows in order or if the Change Buffer effectively sorts for you.
Allocation units:  At first, InnoDB allocates 16KB, but it quickly switches to 16*16KB, then 4MB.  So SHOW TABLE STATUS will show much bigger numbers for Data_length and Index_length, and the excess will not be included in Data_free.


A negative for Hashes:  They are notoriously "random".  That is, caching (the buffer_pool or key_buffer) becomes useless once the data (or index) grows much beyond the cache size.

If you are going to use an MD5 (or SHA1), pack it into BINARY(16) (or (20)) using HEX(). to save space.  Even doing that is not enough for me to recommend hash over an avg of 20 bytes in a VARCHAR.

Another tip:  Consider dropping the AUTO_INCREMENT and promoting the VARCHAR (or hash) to be the PRIMARY KEY.  In many (not all) situations, performance and space consumption both improve.