#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2004
    Posts
    566
    Rep Power
    12

    tinyint(1) NULL or -1


    Hi,
    In my registration form I have a field "show_email" (0 = no, 1 = yes)

    In my database schema I am not sure how to set that field.
    I have 2 choices:
    show_email tinyint(1) NOT NULL DEFAULT -1,
    or this:
    show_email tinyint(1),


    When I retreive values from MySQL table and there was no value set(user forgot or intentionaly missed) for "show_email"
    I get values either
    -1 (in case I go with tinyint(1) NOT NULL DEFAULT -1, )
    or
    NULL (in case I go with tinyint(1), )


    My questions:

    In MySQL:
    Does it make any difference as far as performance and SELECT statements
    if I have NULL or -1 in MySQL database "show_email" field as a default value.

    In PHP
    Is it easier(faster) to deal with NULL or -1 values?

    ----------------------------------
    I am interested to know what is the better approach?
    I would like to know because I have at least 20 fields like that in my database schema.

    Thank you.
    Last edited by sqluppo; July 19th, 2007 at 05:31 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,339
    Rep Power
    4280
    Originally Posted by sqluppo
    In MySQL:
    Does it make any difference as far as performance and SELECT statements
    if I have NULL or -1 in MySQL database "show_email" field as a default value.
    nope
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2006
    Location
    Chandigarh, India
    Posts
    311
    Rep Power
    366

    Spam


    Why not you go with the enum type field. Since you have many such fields and enum will be more meaningful than -1 or NULL etc.


    EDIT: And i just noticed that the same question is asked in PHP forums.
    http://forums.devshed.com/php-develo...1t-460943.html
    Last edited by sumityadav; July 20th, 2007 at 08:28 AM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,339
    Rep Power
    4280
    ENUM is evil and should be avoided

    and in this case, ENUM for a field which has yes/no values is overkill
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2006
    Location
    India
    Posts
    857
    Rep Power
    549
    Originally Posted by r937
    ENUM is evil and should be avoided

    and in this case, ENUM for a field which has yes/no values is overkill
    Hi Rudy!
    How & why is this evil? Is there any logical explanation for this, if somewhere..
    Akash Dwivedi
    "Whatever the mind can conceive and believe, the mind can achieve."
    Feel good..


  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,339
    Rep Power
    4280
    Originally Posted by helloakash
    Hi Rudy!
    How & why is this evil? Is there any logical explanation for this, if somewhere..
    see this post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    I AM A GOLDEN GOD
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Apr 2003
    Location
    Camarillo, California
    Posts
    5,932
    Rep Power
    1166
    Just FYI, this is a crosspost of one in PHP that was over-analyzed and discussed yesterday.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,339
    Rep Power
    4280
    Originally Posted by lnxgeek
    Just FYI, this is a crosspost of one in PHP that was over-analyzed and discussed yesterday.
    i would gladly have merged the two threads some time ago, but i do not have moderator privileges in the php forum and so could not

    and if they were merged now, the conversation would be a right mess, wouldn't it

    tip: if the same question is cross-posted, please report it (the little red triangle) as soon as possible -- and don't reply in the wrong forum!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2006
    Location
    Chandigarh, India
    Posts
    311
    Rep Power
    366
    I mentioned that above in the post when i saw the similar question and i think before me someone already reported the post as SPAM.

    Rudy, could you close/lock this thread so that no one can reply to this.

    Originally Posted by r937
    i would gladly have merged the two threads some time ago, but i do not have moderator privileges in the php forum and so could not

    and if they were merged now, the conversation would be a right mess, wouldn't it

    tip: if the same question is cross-posted, please report it (the little red triangle) as soon as possible -- and don't reply in the wrong forum!
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,339
    Rep Power
    4280
    i would prefer if someone locked the thread in the php forum

    after all, the topic is mysql-related, isn't it?

    Comments on this post

    • sumityadav agrees : Obviously its MySQL related. :)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    I AM A GOLDEN GOD
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Apr 2003
    Location
    Camarillo, California
    Posts
    5,932
    Rep Power
    1166
    Originally Posted by r937
    i would gladly have merged the two threads some time ago, but i do not have moderator privileges in the php forum and so could not
    ...
    tip: if the same question is cross-posted, please report it (the little red triangle) as soon as possible -- and don't reply in the wrong forum!
    ...
    after all, the topic is mysql-related, isn't it?
    Oddly enough, I 'applied' for mod status in the PHP forum awhile back and never heard a word about it. Apparently I can't be trusted with mod status. The main reason I offered to be a mod there was for situations like this: continuous crossposts and threads started in the wrong forum.

    Agreed. I'm usually very good about reporting these, I simply forgot. We got a bit of momentum in the other forum and just went with it.

    Yes, it definitely is MySQL related, as I pointed out in the thread. I just failed to act on it.

    Comments on this post

    • jcarouth agrees : i too applied for mod status in php, but i was also ignored
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2004
    Posts
    566
    Rep Power
    12
    I have placed this post on PHP and MYSQL because I was interested to hear from both sides and get a right solution to my particular problem that involves PHP and MYSQL.
    If you read my post at PHP forum I was interested to find out what is better as far as PHP Engine handling retrieved MySQL values(NULL or -1)

    I described my problem as a whole(PHP & MySQL) so a reader could better inderstand what I mean.
    I would like to find a solution for my PHP MySQL project - not just for one part.

    Sometimes on the forum I get answers like Why I would wanna do something some particular way
    so in this case I decided to describe my problem as a whole.(PHP & MySQL)

    For example in my PHP post people could not understand this:
    "When I retreive values from MySQL table and there was no value set(user forgot or intentionaly missed) for show_email field"
    - instead of trying to answer my problem some people were questioning my
    motives - why would I want to do it this way , what "forgot or intentionaly missed" means and so on.

    I thought my topic was not just mysql-related. It is not my intention to cross-post or spam.
    I just try to learn something.

    Back to the problem:
    I read somewhare that there is some overhead(INSERT, UPDATE) when MySQL deals with NOT NULL restrictions e.g.
    show_email tinyint(1) NOT NULL DEFAULT -1,
    as opposed to just simple
    show_email tinyint(1)

    Is that true?
    Last edited by sqluppo; July 20th, 2007 at 12:18 PM.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,339
    Rep Power
    4280
    no, it isn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,148
    Rep Power
    1317
    Originally Posted by sqluppo
    I have placed this post on PHP and MYSQL because I was interested to hear from both sides and get a right solution to my particular problem that involves PHP and MYSQL.
    and the forum guidelines specifically ask for people not to cross post for just the very reason that there are now two threads with similar answers that would be better off as one.
  28. #15
  29. No Profile Picture
    I AM A GOLDEN GOD
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Apr 2003
    Location
    Camarillo, California
    Posts
    5,932
    Rep Power
    1166
    Originally Posted by sqluppo
    I described my problem as a whole(PHP & MySQL) so a reader could better inderstand what I mean.
    I would like to find a solution for my PHP MySQL project - not just for one part.
    I can appreciate that, but you should only post in one forum, then let the senior members and moderators decide if it's in the wrong forum or not and move the thread.

    Originally Posted by sqluppo
    Sometimes on the forum I get answers like Why I would wanna do something some particular way
    so in this case I decided to describe my problem as a whole.(PHP & MySQL)

    For example in my PHP post people could not understand this:
    "When I retreive values from MySQL table and there was no value set(user forgot or intentionaly missed) for show_email field"
    - instead of trying to answer my problem some people were questioning my
    motives - why would I want to do it this way , what "forgot or intentionaly missed" means and so on.
    As one of those people, I have to say that our determination to steer you in the right direction was directly based on your lack of knowledge. No offense. Your example, as you yourself admitted, was a bad one. We looked at that and said "why are you using a boolean switch in that way", because we know what we're doing. It's as if you have 100 people telling you something is red, and rather than accepting it, you're determined that it's really orange.

IMN logo majestic logo threadwatch logo seochat tools logo