Is it *really* worth to use integer over varchar for a set of data?

Question

For example if I have a table User, I want to store gender or sex, I'll add a column like sex.

Is it really worth to use an integer and then map it in my favorite programming language?

Like 1 => 'Male' and 2 => 'Female'

Is there any performance reason to do that?

Or could I safely use a varchar which more meaning with 'female' or 'male' almost like I was using mysql ENUM ?

Edit: I here and there that it is sometimes better, sometimes it doesn't matter, so I more looking for benchmark or something over a "it is better" answer.

I mean I think using varchar is actually more meaningfull than an integer, and I would use an integer only if performance are more than 0.3% or something.

Answer 1

If this is for some homebrew website or application that will serve 10 people, then do whatever you want, performance won't make a difference.

If this is for something real then skip rolling your own implementation of gender and follow the ISO standard for sex. Or at least adhere to standards wherever they exist (thanks Joe Celko!)

0 = not known
1 = male
2 = female
9 = not applicable

Always rightsize your data type

• Disk space savings:
  At my last job, the pedantic people in charge of designing tables created a column as decimal with 0 precision because it should only be N digits. The difference in storage cost between that and a whole number data type was 1 or 2 bytes. However, as this table was very large the aggregate cost savings of having the smaller data type was measure in gigabytes on the table alone.

• Access savings: A second cost that most don't think about is the cost to read information from disk or to keep data in memory. In SQL Server, data is stored in 8K pages. If you are using fat data types, it will take more reads to get data off disk and then you can store subsequently fewer data pages in memory. Pulling data off of disk is where you will incur the biggest performance cost. If you want to speed up things that use a database, don't bone the physical implementation.

Implement as the smallest allowable type in your system that will cover the problem domain. For something like gender, use a tinyint (MySQL, SQL Server) or number(5,0) in Oracle and you'll be spending 1 bye of storage for each gender.

Internationlization

M = Male, F = Female, that seems obvious. ¿Verdad? Aqui, nosotros hablamos español. And that's about as far as my Spanish caries me, but my point is that if you ever need to be multi-lingual, 1 will serve males, gentes, mannlich, masculin, etc. M or Male will only serve an English speaking audience. Further more, then you run into weird presentation logic of "We need to translate everything unless it's going to $culture." It is a far cleaner design to have presentation logic is the UI and keep it out of the database.

Answer 2

Ortiginal Answer:
I would suggest storing it in a CHAR(1) column as M or F
It is expressive enough for the specific purpose AND has the speed benefit of being a single character comparison

Update 4 (fixed benchmark):
All previous benchmarks had a fatal flaw that one (the CHAR(1)) table was MyISAM and all other were InnoDB. So I recreated the database with all tables using the MyISAM and the results make much more sense now.

The error creeped in as I used the MySQLWorkbench's wizard to create the tables and forgot to change the database engine in the other tables and it defaulted to InnoDB (I have MySQL 5.5)

So the corrected results are as follows, (I have removed all my previous benchmarks as they were invalid) :

// select queries
$query['char'] = "select count(*) from test_table where gender = 'M'";
$query['char_lower'] = "select count(*) from test_table where LOWER(gender) = 'm'";
$query['varchar'] = "select count(*) from test_table_2 where gender = 'Male'";
$query['varchar_lower'] = "select count(*) from test_table_2 where LOWER(gender) = 'male'";
$query['tinyint'] = "select count(*) from test_table_3 where gender = 1";

// benchmark result
array
  'char' => float 0.35457420349121
  'char_lower' => float 0.44702696800232
  'varchar' => float 0.50844311714172
  'varchar_lower' => float 0.64412498474121
  'tinyint' => float 0.26296806335449

New Conclusion : TINYINT Is fastest. But my recommendation would be still yo use CHAR(1) as it would be easier for future developers to understand the database.

If you do use TINYINT, my recommendation would be name the column ismale instead of sex and store 0 => Female and 1 => male thus making it a little more easy to understand in raw database.

The table structure for benchmark is this:

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

Only the type of the gender column is different in the 3 tables, the types are:

CHAR(1), VARCHAR(6), TINYINT

All 3 tables have 10000 entries.