4 ms·

Why does he say varchar(255) is bad design?

petercooper 18y ago
Because 255 is now just an arbitrary choice for a VARCHAR length. Explanation: Prior to MySQL 5.0.3 (give or take a few point releases - I forget) a VARCHAR column could be 255 characters in length maximum, so VARCHAR(255) was often used as a default. Now, however, you can go up to 65,535 characters on VARCHAR, so if you're still using "255" then that seems arbitrary and not well thought out (or your schema is just old).
- ars 18y ago
  That's not totally true. Using a varchar of 255 or less saves one byte vs one over 255. So if you don't know any particular size to use, 255 is reasonable. And varchar(20) vs varchar(255) for a 10 character string use _exactly_ the same space. So there is no reason to artificially use a smaller value.
  - bayareaguy 18y ago
    It depends on what you mean by artificial. It's a good idea to use a smaller value if you have an idea of what the actual limit should be for you want the column to contain.
  - petercooper 18y ago
    > So there is no reason to artificially use a smaller value. That's not true either. As the document explains, MySQL's internal buffering uses fixed sized columns matching the length limit of the VARCHAR column. So a VARCHAR(255) will cause 255 bytes of memory to be allocated versus only 20 for VARCHAR(20). Of course, it depends on what range of sizes your data fit as to what you do :)
    - ronaldbradford 18y ago
      This is also multiplied by the character encoding, so when you have a table with 10 columns at VARCHAR(255) because your too lazy to define them, and they are all UTF8, so that's 255*3 for each column for each row when used in a MySQL Internal buffer (not, these buffers may or may not be used depending on the type of query). When you have 100s to 1000s of queries per second, knowing and using your memory wisely is very important, because MySQL unlike Oracle for example, does not cap the amount of memory used for the Process, again using the Oracle approach the PGA (Process Global Area). It's always easy to take sort cuts in design, when your DB will never grow above a few GB's and your users will never grow above say 10. VARCHAR(255) is just as much a sign of little to no design, and therefore this and many other beginner 101 mistakes are likely, and therefore large performance gains are generally easily made in a system that now is needed to perform.
      - gaius 18y ago
        Kinda the point of UTF encoding is it's variable-width; if you only need ASCII then that's all you'll store. The MySQL people seem to have chosen to use the worst possible case every time regardless; this is not a "feature".
jlouis 18y ago
If the MySQL documentation is right, it doesn't matter at all in MySQL. VARCHARs up to 255 uses an extra byte for counting the size of the column. There is a reading problem with variable-size records of any kind compared to fixed-size records. It goes to say that other databases uses different allocation strategies for VARCHARs so the advice can't be carried over. For instance, they might define a fixed-size area together with an optional overflow area. This way one can make it such that most records have a fixed size and gain speedup.

Because 255 is now just an arbitrary choice for a VARCHAR length. Explanation: Prior to MySQL 5.0.3 (give or take a few point releases - I forget) a VARCHAR column could be 255 characters in length maximum, so VARCHAR(255) was often used as a default. Now, however, you can go up to 65,535 characters on VARCHAR, so if you're still using "255" then that seems arbitrary and not well thought out (or your schema is just old).

ars 18y ago
That's not totally true. Using a varchar of 255 or less saves one byte vs one over 255. So if you don't know any particular size to use, 255 is reasonable. And varchar(20) vs varchar(255) for a 10 character string use _exactly_ the same space. So there is no reason to artificially use a smaller value.
- bayareaguy 18y ago
  It depends on what you mean by artificial. It's a good idea to use a smaller value if you have an idea of what the actual limit should be for you want the column to contain.
- petercooper 18y ago
  > So there is no reason to artificially use a smaller value. That's not true either. As the document explains, MySQL's internal buffering uses fixed sized columns matching the length limit of the VARCHAR column. So a VARCHAR(255) will cause 255 bytes of memory to be allocated versus only 20 for VARCHAR(20). Of course, it depends on what range of sizes your data fit as to what you do :)
  - ronaldbradford 18y ago
    This is also multiplied by the character encoding, so when you have a table with 10 columns at VARCHAR(255) because your too lazy to define them, and they are all UTF8, so that's 255*3 for each column for each row when used in a MySQL Internal buffer (not, these buffers may or may not be used depending on the type of query). When you have 100s to 1000s of queries per second, knowing and using your memory wisely is very important, because MySQL unlike Oracle for example, does not cap the amount of memory used for the Process, again using the Oracle approach the PGA (Process Global Area). It's always easy to take sort cuts in design, when your DB will never grow above a few GB's and your users will never grow above say 10. VARCHAR(255) is just as much a sign of little to no design, and therefore this and many other beginner 101 mistakes are likely, and therefore large performance gains are generally easily made in a system that now is needed to perform.
    - gaius 18y ago
      Kinda the point of UTF encoding is it's variable-width; if you only need ASCII then that's all you'll store. The MySQL people seem to have chosen to use the worst possible case every time regardless; this is not a "feature".

jlouis 18y ago

If the MySQL documentation is right, it doesn't matter at all in MySQL. VARCHARs up to 255 uses an extra byte for counting the size of the column. There is a reading problem with variable-size records of any kind compared to fixed-size records. It goes to say that other databases uses different allocation strategies for VARCHARs so the advice can't be carried over. For instance, they might define a fixed-size area together with an optional overflow area. This way one can make it such that most records have a fixed size and gain speedup.