CODEWITHSUNDEEP
mysqltypes
markzzz
markzzz

Reputation: 47945

tinyint or varchar(1)?

I have some fields in some tables that can contain only value from 0 to 9, and other that can contain 0 or 1.

For your experience, is it better to store it with TINYINT or VARCHAR(1) ?

As memory usage, speed to recognize them, and so on...

Upvotes: 8

Views: 7257

Answers (3)

ChoNuff
ChoNuff

Reputation: 822

If you can use a TINYINT(1), you're better off using that, as a VARCHAR(1) takes up twice the amount of space, per row; TINYINT(1) uses only 1 byte per row, whereas VARCHAR(1) uses 1 byte for the length, and then another byte for the actual data. This doubles the amount of data used in querying, and also any associated indexes, etc.

Upvotes: 1

Eugene Yarmash
Eugene Yarmash

Reputation: 149796

It depends on how you are going to use the values: as strings or as numbers. If you're going to use them as numbers, TINYINT(1) is definitely better as mysql won't need to do unnecessary conversions. For 1-character strings you could use CHAR(1).

Upvotes: 11

Henk Holterman
Henk Holterman

Reputation: 273244

It shouldn't matter a whol lot, although I would choose CHAR(1) over varchar.

If you use the value as a number, ie you're going to Add or Subtract them, use TinyInt.

Otherwise, try to look ahead. Is there a chance there will ever be new values? What would be the range then?

Upvotes: 3

Related Questions