17

A really quick one - is it more efficient to store data which might be one or two characters as CHAR(2) or VARCHAR(2) in MySql?

Thanks!

razlebe
  • 7,134
  • 6
  • 42
  • 57
James
  • 7,343
  • 9
  • 46
  • 82
  • The two data types are not equivalent. Are you asking which is more efficient to store exactly-2-character strings? – Matthew Apr 13 '11 at 17:46

4 Answers4

23

In terms of storage space required, you're better off with CHAR(2) because the VARCHAR(2) type will require one extra byte to store the length:

Value   CHAR(2)  Storage Required  VARCHAR(2)  Storage Required
''      '  '     2 bytes           ''          1 byte          
'a'     'a '     2 bytes           'a'         2 bytes         
'ab'    'ab'     2 bytes           'ab'        3 bytes         

See 10.4.1. The CHAR and VARCHAR Types for more details.

More Information: What's the difference between VARCHAR and CHAR?

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
6

In terms of space, CHAR(2) is better. See this: http://dev.mysql.com/doc/refman/5.0/en/char.html

If you had longer like CHAR(100) vs VARCHAR(100), which is better in space usage depends on the data you have.

In any case, in terms of efficieny of queries, it is always a good idea to have fixed length records when you can afford to have them (the DB can optimize better for fixed length columns).

So CHAR(2) looks like a win over VARCHAR(2) in terms of space and time.

user127.0.0.1
  • 1,337
  • 10
  • 18
0

I would ask, however, is the efficiency difference between varchar and char worth it? Seems like any performance gains would be, at best, negligible.

MTR
  • 9
  • 2
  • 2
    This is more of a comment than an answer... as to whether or not it makes a difference, it's going to depend on the use case. With a length of 2, there's a 50% overhead on storage per field for VARCHAR2. If there's a large number of similar fields and rows, it's going to start adding up fast. – forsvarir Apr 13 '11 at 19:26
0

In storage, VARCHAR(255) is smart enough to store only the length you need on a given row, unlike CHAR(255) which would always store 255 characters.

Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155