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 at 12:34

Your Answer

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

Browse other questions tagged or ask your own question.