CODEWITHSUNDEEP
mysql
JonoB
JonoB

Reputation: 5887

Does significantly increasing the length of varchar fields impact on performance?

At the moment, we have a varchar(255) field. One of our users has requested that the field be increased about 10-fold to around varchar(2048). Given his use-case, it appears to be a reasonable request.

The field in question is not indexed, it is not part of any joins and it is never included in any where clauses.

I realize that smaller increases (say from 50 to 100) have no impact, but does this still hold at much larger increases in size?

Upvotes: 5

Views: 1257

Answers (2)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6726

For your case there is no difference between varchar(255) and varchar(2048).

In MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.

Upvotes: 2

Francisco Presencia
Francisco Presencia

Reputation: 8851

Apparently not with a sidenote:

"In storage, VARCHAR(255) is smart enough to store only the length you need on a given row, unlike CHAR(255) which would always store 255 characters.

But since you tagged this question with MySQL, I'll mention a MySQL-specific tip: when your query implicitly generates a temporary table, for instance while sorting or GROUP BY, VARCHAR fields are converted to CHAR to gain the advantage of working with fixed-width rows. If you use a lot of VARCHAR(255) fields for data that doesn't need to be that long, this can make the temporary table very large.

It's best to define the column based on the type of data that you intend to store. It's hard to know what the longest postal address is, of course, which is why many people choose a long VARCHAR that is certainly longer than any address. And 255 is customary because it may have been the maximum length of a VARCHAR in some databases in the dawn of time (as well as PostgreSQL until more recently)."

by Bill Karwin

So it basically depends on your specific user for that field; If you don't use GROUP BY with that field, then there's no problem.

Upvotes: 2

Related Questions