varchar(255) field or hash field?

Question

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?

Answer 1

[In this answer, I assume using MD5 as hashing function]

The answer is YES. Adding a "hash" field and querying it would run faster.

Details: When indexing a varchar(255) field, although the average length is 20 char, each entry in the index will be saved in its full length, i.e. 255 char. Add to this that if you are using utf8, the entry length would be 255*3 bytes (plus the PK length).

When adding a hash field, make sure it has a fixed length (32 in case of MD5), and that the CHARSET is latin, i.e. 1 byte per char. In this case, the entry in the index will be 32 bytes (plus the PK length)

If you want to guarantee the uniqueness of foo field, it is recommended to add a unique index on the hash field (as opposed to a regular index)

Answer 2

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.