1

I'm modelling a database and I want to ask about the Varchar type.

Is there any performance difference between Varchar(50) and Varchar(100)?

For example, I got a varchar(50) field and user saved 5 char data to this field and another User saved 25 char to this field. But no one saved 50 char data to this field. So I have to turn this field varchar(25) for max performance?

halfer
  • 19,824
  • 17
  • 99
  • 186
İbrahim Akgün
  • 1,527
  • 11
  • 37
  • 63

5 Answers5

1

Assuming SQL Server (since you also listed MySQL), no.

The overhead is the same as long as the data in the field is the same.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • 1
    Check out Martin Smith's answer: http://stackoverflow.com/questions/2009694/is-there-an-advantage-to-varchar500-over-varchar8000/5654947#5654947 – gbn Oct 10 '11 at 19:13
1

For most implementations there may be an inconsequential performance difference between VARCHAR(50) and VARCHAR(25); probably not worth worrying about.

As a rule of thumb, make the field as large as you'd think you'd ever want to use.

Here is some relevant discussion:
What are the optimum varchar sizes for MySQL?

From that discussion, here is the relevant MySQL manual page:
http://dev.mysql.com/doc/refman/5.0/en/char.html

Community
  • 1
  • 1
Matthew
  • 10,244
  • 5
  • 49
  • 104
1

No, there's no performance penalty if you define varchar(50) and you only store a max of 10 characters, to give you an example. Varchar will always store the data in row.

Icarus
  • 63,293
  • 14
  • 100
  • 115
1

EDIT: For MySQL: No. At least, not unless you have some strings between 50-100 characters.

Normally varchars are stored as a length + data; only the data supplied are stored. A varchar(100) takes more bytes for the length (maximum 300 bytes, assuming utf8 3-bytes-per-character), but it's not enough that you care.

It's the same for the indexes. It's only as much as you really store.

MarkR
  • 62,604
  • 14
  • 116
  • 151
1

The column length should reflect the maximum length of the data your users want to store in that column. If your application has been designed on the basis of well-researched requirements then there ought to be some justification for that length; just because nobody is storing strings > 25 characters in that column yet doesn't mean that they won't someday.

To answer the other aspect of your question, there is no performance gain to be made from reducing the length of the column. As the docs have it, the column only takes up storage to fit the data assigned. So a VARCHAR(100) column with only 25 bytes of data won't take up any more storage than a VARCHAR(25) column with the same data. Find out more.

Is storage a performance issue? It can be, because shorter records on disk equals more records retrieved per I/O operation.

APC
  • 144,005
  • 19
  • 170
  • 281