Flag Search: Boolean, Integer or String ?

[acidrock]

Hello,
We use mysql 4.1.13a with myISAM storage engine.
I have googled a lot without success, so I hope somebody will answer
Also, I’m not english fluent, hope it is not too boring to read me )

We have a lot of “flag” in our database.
By flag I mean attribute with “Yes” or “No” (like “this image is in color” or “this image is panoramic”).
I’m looking for the best performance to search on that kind of field.
I see many manner to do it:

• integer (with 0 or 1)
• string (Y or N)
• enum (Y/N or 1/0)

But also I can use NULL way, I mean

• integer (1 and NULL)
• string (Y and NULL)

Whats is the speediest way to perform that kind of search ?

Thanks for your time

[sterin]

Ditch the NULL approach because it just complicates things.

And ditch the ENUM because it makes everything very static and hard to maintain if you want to do any changes.

That leaves us with:
numeric vs string.

And basically you choose any one of them.

But don’t use INT because that takes 4 bytes of storage.
Use TINYINT UNSIGNED NOT NULL if you choose the numeric approach that will only use 1 byte of storage.

Which is the same amount if you choose CHAR(1) NOT NULL.

Performance wise you will not notice any difference between these two.

I usually use a CHAR(1) because it is easier to read, especially if you want several states and not just Y/N. It’s much easier to remember that:
I => means incomplete
than:
2 => means incomplete