Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7

We have an index over a varchar(255) column in a table of ~ 500M rows.

select distinct varchar_column from table yields seven results, with the max length 21.

A DBA consultant recommended we drop the length of the varchar column to 21. Dropping the size of the varchar column in tests shrank the index size equivalently to simply defragmenting the table without changing the schema (e.g. alter table).

We saw marginally better results when we dropped the length to 17.

Are there any advantages we're missing besides index size?

If some of the values are 21 characters, dropping to 17 will lose data. You probably don't want to do that.

Be conservative -- what will future data be like? Maybe 22 characters? Drop the length to, say, 30.

No space will be saved by changing the max length -- in the "data" or in the "index". Well, not because of the change. However, most ALTERs include some degree of rebuilding the table. This is likely to squeeze out some, not all, of the freed or wasted space. To see what I mean,

SHOW TABLE STATUS LIKE 't';   -- current size
OPTIMIZE TABLE t;
SHOW TABLE STATUS LIKE 't';   -- defragmented size
ALTER TABLE t MODIFY COLUMN c VARCHAR(30)...;`
SHOW TABLE STATUS LIKE 't';   -- probably no change to Data_length or Index_length

Performance? In a complex SELECT, where a temporary table needs to be built, VARCHARs turn into CHARs temporarily. For utf8mb4, that is 4255 bytes versus 430. This leads to bulkiness. If the temp table is too big for RAM, it will spill to disk. Disk is slower, even SSDs.

StackExchange Database Administrators Q#239326, answer score: 4