4

I will always be entering things of 20 characters into a particular column in my table.

I need this column to be unique.

Will there be any speed difference in SELECT queries if I set this column to be a varchar(255) instead of varchar(20)?

(data entered will always be 20 characters)

David19801
  • 11,214
  • 25
  • 84
  • 127

5 Answers5

5

if data entered will always be 20 characters than why not consider using char(20). using varchar(20) will use 20 bytes for storing character and 1 byte for storing length. so if there are 1 million records, 1 million bytes will be wasted.

as far as speed is concerned between varchar(20) and varchar(255), then I dont think it might be very hard to pick one of them, both of them will be using 21 bytes, I dont see any significant performance benefit or loss of one over other.

Zohaib
  • 7,026
  • 3
  • 26
  • 35
3

If it can only be 20, why would you want to specify 255? If it is always 20, even char(20) would be better.

Cylindric
  • 5,858
  • 5
  • 46
  • 68
  • Future proofing (we might upgrade SLI codes next year). Also, I am curios to see what the answer will be. – David19801 Dec 02 '11 at 11:33
  • 1
    Don't future-proof! Do you think column widths are carved in stone? If you need to change in the future, change it *in* the future. Might as well set it to varchar(5000) now, just in case. – Cylindric Dec 02 '11 at 11:42
  • Is there any reason I should not set it to varchar(5000) I thought the max was 255? – David19801 Dec 02 '11 at 11:59
  • My point was, don't just set everything to MAX on the off-chance that one day you might need it. Especially not when it is generally very easy to increase the size of a column, compared to reducing it. – Cylindric Dec 02 '11 at 14:03
2

in select query you are not gain speed increasing by reducing length of varchar but you can increase speed of insertion a new record

triclosan
  • 5,578
  • 6
  • 26
  • 50
2

If you are pretty sure that all values will be 20 chars and they will be of fixed lenght then do go with char(20) as you will gain space and a slight amount of speed.

Value       CHAR(4)  Storage Required   VARCHAR(4)  Storage Required
''          '    '           4 bytes            ''            1 byte
'ab'        'ab  '           4 bytes          'ab'           3 bytes
'abcd'      'abcd'           4 bytes        'abcd'           5 bytes
'abcdefgh'  'abcd'           4 bytes        'abcd'           5 bytes

The above table is taken from MySQL Manual and I do advise you to read the comments on that thread (sample bellow)

Posted by Kirby Wirby on April 9 2007 8:33pm

  • Consider the case of an indicator field where the value is either 'Y' or 'N'. If defined as a CHAR, the field requires only one byte. However, if defined as a VARCHAR, the field requires two bytes.
Frankie
  • 24,627
  • 10
  • 79
  • 121
0

Best thing is to allocate as much space as it needs, cause MySQL often allocates fixed-size chunks of memory to hold values internally.

This is bad for sorting or operations that use in-memory temporary tables. The same thing happens with filesorts that use on-disk temporary tables. The book High Performance MySQL gives more information about these issues. I advise to read it.

Kheldar
  • 5,361
  • 3
  • 34
  • 63
my_point
  • 9
  • 2