3

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?

  • 2
    estimated memory grants? At least with MSSQL the estimated memory grant for a VARCHAR is 50% of the max size. This would save quite a bit of estimated rows (and maybe give a better plan) by correctly sizing the column. – Jonathan Fite May 29 '19 at 12:34
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.