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

Question

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?

Answer 1

There will be some CPU churn when using VARCHAR columns because length computations must be done. This is true regardless of VARCHAR(21) or VARCHAR(255). If you are look for greater speed, you must change the VARCHAR(21) to CHAR(21).

This will make row lengths and indexes bigger, but you will see much better results. The tradeoff of size for speed is well worth it.

I discussed this before

• May 10, 2011 ; What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
• Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

For those using MySQL 5.x, you should determine the right size and type using

select distinct varchar_column from table PROCEDURE ANALYSE();

See MySQL 5.7 Docs on PROCEDURE ANALYSE(). (It's deprecated in 8.0).

Answer 2

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 4*255 bytes versus 4*30. This leads to bulkiness. If the temp table is too big for RAM, it will spill to disk. Disk is slower, even SSDs.