9

I usually set all my varchars to 255 to be safe. Does it make any difference in terms of the disk space or anything else? Is there any downside to having bigger varchars/ints/other fields than you would mostly need?

Ali
  • 261,656
  • 265
  • 575
  • 769
  • possible duplicate of [Does VARCHAR size limit matter?](http://stackoverflow.com/questions/4324872/does-varchar-size-limit-matter) – Marek Karbarz Apr 28 '11 at 16:10
  • Duplicate of http://stackoverflow.com/questions/262238/are-there-disadvantages-to-using-a-generic-varchar255-for-all-text-based-fields – Capsule Apr 28 '11 at 16:11
  • possible duplicate of [Importance of varchar length in MySQL table](http://stackoverflow.com/questions/1962310/importance-of-varchar-length-in-mysql-table) – wallyk Apr 28 '11 at 16:11
  • Edited my question, this is about all fields not just varchars like the other questions. – Ali Apr 28 '11 at 16:12

5 Answers5

5

If you allow 255 characters then someone may use 255 characters. And so every report and online data entry screen has to be able to handle 255 characters. Messy.

I have a real-world example: in our system someone decided that department names could be 200 chars long. When someone put in a rather long value, it screwed up the layout of several HTML forms, pushing other fields off the right hand side and making them inaccessible. So some remedial work was required...

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 2
    Rather have that problem than try to dictate how many characters someone's name should have – Ali Apr 28 '11 at 16:16
  • I think there is a middle ground here where you model the column size to the max realistic data. E.g. what names do you come across for your data type that would approach let alone exceed say 60 chars? "The institute of ridiculously long named thing-a-ma-bobbers"... ok, maybe... but 200 is way more than needed 99.999% of the time. – scunliffe Feb 17 '14 at 16:17
4

It takes more time and more disk transfers to load larger data items into memory. Defining large maximum sizes for columns increases the size of table rows. For many DBMS servers, table rows are the items transferred. So defining columns that are too fat does slow things down.

This effect is minimal for VARCHAR items. But VARCHAR is quite a bit slower than data types like integers. Eight byte integers take four times as much time to transfer as two byte integers. So, if a database is being designed for ultimate performance, limiting data columns to the range actually required will speed things up.The extent of this effect depends on whether the disk channel is a bottleneck or not.

Another possible bottleneck is the channel that links the server with the client, often a network channel. Bottlenecking in this channel can be reduced by queries that don't ask for data that will never be used, but there's a trade off here between asking for data only when you need it and making too many round trips.

There's also a trade off between designing for optimal performance and over designing in the anticipation of changing requirements.

d-_-b
  • 21,536
  • 40
  • 150
  • 256
Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
3

No that does not effect any disk space. If you choosed any column to be varchar that will be variable length from 0 to 65535. if you declare it 255 or 65535 they both are same.

if you needs only 255 chars you should declare them as char so that if there is no variable length column in your table your queries will be fast.

Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
  • 2
    According to this it makes a difference on queries:http://stackoverflow.com/questions/1151667/what-are-the-optimum-varchar-sizes-for-mysql – Mikecito Apr 28 '11 at 16:11
1

With disk space as cheap as it is, I tend to not be as concerned over this as I was years ago. However, little things add up in VLDB's.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
  • 2
    Disk space is cheap but RAM isn't. According to http://stackoverflow.com/questions/262238/are-there-disadvantages-to-using-a-generic-varchar255-for-all-text-based-fields this has a huge impact on temporary tables which won't fit in memory anymore -> temporary files are created and reduce the performance of the query. – Capsule Apr 28 '11 at 16:15
  • Good point, again. This will not matter much for a DB that is a few hundred MB's. But my point about scalability is across the board for all resources, when you are talking millions of records and TB's then every little `bit` helps. (pardon the pun) – Dustin Laine Apr 28 '11 at 16:17
0

This might give insight:

What are the optimum varchar sizes for MySQL?

Community
  • 1
  • 1
Mikecito
  • 2,053
  • 11
  • 17