CODEWITHSUNDEEP
mysqldatabasetypes
James
James

Reputation: 7533

Which is more efficient for a one or two-character string: CHAR(2) or VARCHAR(2)?

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!

Upvotes: 16

Views: 7781

Answers (4)

Mark Byers
Mark Byers

Reputation: 838376

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?

Upvotes: 22

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36631

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.

Upvotes: 0

MTR
MTR

Reputation: 9

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

Upvotes: 0

user127.0.0.1
user127.0.0.1

Reputation: 1337

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.

Upvotes: 5

Related Questions