Not the answer you need?
Register and ask your own question!

Flag Search: Boolean, Integer or String ?

acidrockacidrock EntrantInactive User Role Beginner
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

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.