CODEWITHSUNDEEP
mysqloptimizationbooleandatabase-schema
GJ.
GJ.

Reputation: 5364

Alternatives to boolean flags in MySQL tables?

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.

Upvotes: 2

Views: 892

Answers (2)

Quassnoi
Quassnoi

Reputation: 425411

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.

Upvotes: 4

Haim Evgi
Haim Evgi

Reputation: 125496

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)

Upvotes: 2

Related Questions