Announcement Module
No announcement yet.

Flag Search: Boolean, Integer or String ?

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Flag Search: Boolean, Integer or String ?

    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

  • #2
    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
    2 => means incomplete