Are there disadvantages to using a generic varchar(255) for all text-based fields?

by

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: as rows are copied from the storage engine layer to the SQL layer, VARCHAR fields are converted to CHAR to gain the advantage of working with fixed-width rows. So the strings in memory become padded out to the maximum length of your declared VARCHAR column.

When your query implicitly generates a temporary table, for instance while sorting or GROUP BY, this can use a lot of memory. 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.

You may also like to know that this “padding out” behavior means that a string declared with the utf8 character set pads out to three bytes per character even for strings you store with single-byte content (e.g. ascii or latin1 characters). And likewise utf8mb4 character set causes the string to pad out to four bytes per character in memory.

So a VARCHAR(255) in utf8 storing a short string like “No opinion” takes 11 bytes on disk (ten lower-charset characters, plus one byte for length) but it takes 765 bytes in memory, and thus in temp tables or sorted results.

I have helped MySQL users who unknowingly created 1.5GB temp tables frequently and filled up their disk space. They had lots of VARCHAR(255) columns that in practice stored very short strings.

It’s best to define the column based on the type of data that you intend to store. It has benefits to enforce application-related constraints, as other folks have mentioned. But it has the physical benefits to avoid the memory waste I described above.

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 is the maximum length of a VARCHAR for which the length can be encoded with one byte. It was also the maximum VARCHAR length in MySQL older than 5.0.

More Related Contents:

  1. how to delete data from table A besides the customer service and director data in table B?
  2. SQL split values to multiple rows
  3. Get records with max value for each group of grouped SQL results
  4. How to populate a table with a range of dates?
  5. MYSQL OR vs IN performance
  6. SELECTING with multiple WHERE conditions on same column
  7. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  8. MySQL Cannot Add Foreign Key Constraint
  9. How to find gaps in sequential numbering in mysql?
  10. MySQL: Invalid use of group function
  11. Convert timestamp to date in MySQL query
  12. Update a column value, replacing part of a string
  13. MySQL: View with Subquery in the FROM Clause Limitation
  14. MySQL/MariaDB – order by inside subquery
  15. View’s SELECT contains a subquery in the FROM clause
  16. Can MySQL replace multiple characters?
  17. OUTPUT Clause in MySQL
  18. Using ORDER BY and GROUP BY together
  19. Sorting by date & time in descending order?
  20. MySQL Pivot Table Column Data as Rows
  21. updating columns with a sequence number mysql
  22. MySQL, Concatenate two columns
  23. Select a column with a keyword name
  24. MySQL Stored Procedures not working with SELECT (basic question)
  25. ERROR 1148: The used command is not allowed with this MySQL version
  26. Use Composite Primary Key as Foreign Key
  27. How to select data where a field has a min value in MySQL?
  28. get a recursive parent list
  29. #1060 – Duplicate column name ‘id’
  30. Update multiple rows with multiple ‘where’ clauses for each individual row

Leave a Comment