Author Topic: VARCHAR and max string length  (Read 778 times)

Offline Topazan

  • Level 14
  • *
  • Posts: 117
  • Reputation: +3/-0
    • View Profile
VARCHAR and max string length
« on: December 08, 2010, 07:23:22 PM »
Is there any reason to restrict the length of a VARCHAR string below the maximum?  My understanding is that with VARCHAR the data storage requirements are determined by the actual length, not the maximum length.  Is this correct?  If so, why would you want to restrict the maximum length of the string?  If not, what's the difference between VARCHAR and CHAR?

Offline JGadrow

  • Level 35
  • **
  • Posts: 1,133
  • Reputation: +23/-2
    • View Profile
Re: VARCHAR and max string length
« Reply #1 on: December 09, 2010, 04:34:51 AM »
Restricting the length of the varchar is a way to make your intent clear. For example, do you really want usernames that are 255 characters in length? 32 or even 20 sounds much more reasonable.

You are correct that the storage is dependent upon the data. The data in a varchar takes 1 byte per character plus one (sometimes 2) extra byte to store the size.

A char field takes a number of bytes equal to the defined size. If you enter a value that does not contain the proper number of characters, spaces are added to increase the length to the required size.

Essentially, char is a more efficient means of storing the maximum total of characters while varchar is more efficient at storing up to max-2 characters.
Idiocy - Never underestimate the power of stupid people in large groups.


Offline Chris

  • Game Owner
  • Level 35
  • *
  • Posts: 2,217
  • Reputation: +28/-1
    • View Profile
Re: VARCHAR and max string length
« Reply #2 on: December 09, 2010, 04:54:55 AM »
ORDER BY (or other sorting/grouping operations) on some storage engines might resize the row to maximum allowed size, which leads to higher memory usage (so for some operations VARCHAR might act like CHAR). I don't recall under which conditions exactly it happens, anyway bigger VARCHAR might perform worse than small one under some circumstances.

Even better, just use CHAR only for a whole table which makes all entries fixed size so finding a new row is trivial and the fastest (again, depending on storage engine and if the field is too big it might be better to use variable sized VARCHAR...)

Offline Nox

  • Level 35
  • **
  • Posts: 767
  • Reputation: +12/-2
    • View Profile
Re: VARCHAR and max string length
« Reply #3 on: December 09, 2010, 05:09:07 AM »
VARCHAR x CHAR is rather internal difference, type with not(less?)restricted length is TEXT/LONGTEXT so use that if you'd like long string to be possible
Meet us at an IRC irc.freenode.net #bbg as well
https://vimeo.com/36579366 (a must-watch) | Join BOINC - no longer a hype, but you can help never the less

Offline JGadrow

  • Level 35
  • **
  • Posts: 1,133
  • Reputation: +23/-2
    • View Profile
Re: VARCHAR and max string length
« Reply #4 on: December 09, 2010, 05:15:47 AM »
VARCHAR x CHAR is rather internal difference, type with not(less?)restricted length is TEXT/LONGTEXT so use that if you'd like long string to be possible

The only issue with these types is indexing is impractical. You shouldn't commonly be indexing non-numeric fields anyways... but in some scenarios it makes sense (state names in the US, declaring a unique email address field, etc.).
Idiocy - Never underestimate the power of stupid people in large groups.


Offline Chris

  • Game Owner
  • Level 35
  • *
  • Posts: 2,217
  • Reputation: +28/-1
    • View Profile
Re: VARCHAR and max string length
« Reply #5 on: December 09, 2010, 08:37:14 AM »
VARCHAR x CHAR is rather internal difference, type with not(less?)restricted length is TEXT/LONGTEXT so use that if you'd like long string to be possible
TEXT/BLOB is not like VARCHAR/CHAR, the first is stored as a pointer (in some other internally created table I suppose?), the second is included in the table body. If you need only 255 chars use VARCHAR, not TEXT. Again, might be not true for some storage engines.

Quote
You shouldn't commonly be indexing non-numeric fields anyways
I agree, also only first X letters will be used (MySQL limit) for index so it might be much less effective than you expect. For index to work efficiently the size is crucial, long integer uses 4 bytes (and this is enough for 4 billion entires), while string use 1 byte per letter (so for equal efficiency you would have index no more than first 4 letters and with 4 letter only you will get much less than 4 billion combinations not to mention that there will be several repetitions like "The[space]" in names of books which might make the index half useless).

Offline Topazan

  • Level 14
  • *
  • Posts: 117
  • Reputation: +3/-0
    • View Profile
Re: VARCHAR and max string length
« Reply #6 on: December 09, 2010, 03:53:29 PM »
Ok, I think I understand a little better now, thanks.

 


SimplePortal 2.3.3 © 2008-2010, SimplePortal