Alternatives to boolean flags in MySQL tables?

Question

I'm using various boolean flags in my tables, and from reading a bit about optimizing performance I came across a widespread tip to avoid using boolean flags.

What are the effective alternatives? Some examples would be much appreciated.

Answer 1

if you have a lot of boolean fieds, you can use bit

like

instead of four fields "true, false, false, true" store one number "9" (1001 in binary)

Answer 2

I'm using various boolean flags in my tables, and from reading a bit about optimizing performance I came across a widespread tip to avoid using boolean flags.

In MySQL, BOOLEAN is just an alias for TINYINT(1).

This means that boolean operations are in fact integer operation.

That, among other things, means that in a query like this:

SELECT  *
FROM    mytable
WHERE   boolean_flag = 0

an index on boolean_flag, if any, may be used, while in this one:

SELECT  *
FROM    mytable
WHERE   NOT boolean_flag

the index will not be used.