SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    MySQL Indexing a 'Status' field

    Hi

    Following is my table DDL

    Code:
    CREATE TABLE `users` (
     `userid` int(11) NOT NULL AUTO_INCREMENT,
     `fullname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
     `status` int(1) NOT NULL,
     `createtimestamp` int(11) NOT NULL,
     PRIMARY KEY (`userid`),
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    and my query to get a list of active users by their creation date is the following

    Code:
    SELECT userid, fullname FROM users WHERE status='1' ORDER BY createtimestamp ASC
    What I want to know are:

    1) What type of indexes do I have to set for the "createtimestamp" field since its used in the query to sort records.

    2) Since the "status" field is INT datatype and holds just 1 and 0, does this field also needs to be indexed? If yes, which index?

    3) Will it do any good if I set the "status" field as ENUM and have "ACTIVE" / "INACTIVE" instead of the current INT with 1 and 0 values? If yes why?



    Thanks for any inputs.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    1) just a normal one

    2) indexing it won't help, because of the cardinality -- but EXPLAINs should be run to confirm

    3) ENUM is the spawn of the devil, please avoid it fastidiously
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply r937

    Mind elaborating pt # 3?



    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    Mind elaborating pt # 3?
    google "ENUM is the spawn of the devil"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    2: Why would an index not help if there are only two options to choose from?

    3: "ENUM is the spawn of the devil"

    In MySQL: definately. MySQL doesn't protect against duplicate options in the ENUM and when you remove an option that is in use, the records that have that option selected are NULLed. Yikes.

    In other databases though... I'm not so sure. It depends on how it is applied, and why.

    Generally I'd use a lookup when there is even a hint that the options might some day need to be changed by the end-user, or it is glaringly obvious that the options are going to be used for sorting (allthough a CHECK constraint can do that too) and when the value of the ENUM is linked to more information. The status for example is generally not 0 or 1, it is "active" or "inactive" and you will want to show "inactive" in the user interface, but store "0" in the database. ENUM can't do that.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    2: Why would an index not help if there are only two options to choose from?
    because the optimizer will ignore the index, since a table scan is likely faster

    re ENUM...

    yes, any user changes to the ENUM values requires LATER TABLE privileges, which is not soimething you really want to offer users

    also, mysql doesn't yet support CHECK constraints
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    because the optimizer will ignore the index, since a table scan is likely faster
    How does that follow from the fact that there are only 0's and 1's? The planner will look at how many records the index is likely to filter by looking at the statistics of how many 0's and 1's there are, not the fact that there are only 0's and 1's. (unless this is a MySQL thing, but I doubt that :-) )

    yes, any user changes to the ENUM values requires LATER TABLE privileges, which is not soimething you really want to offer users
    Indeed, ENUM's are meant for the DBA to lock the options in place.

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I love enum I use it for all my yes/no needs
    <cfset myblog = "http://cydewaze.org/">

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I love enum I use it for all my yes/no needs
    Do you think you will ever take an SQL class and learn how to code databases properly? Do you do anything else the worst possible way or just your database tables?
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  10. #10
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I love enum I use it for all my yes/no needs
    Do you think you will ever take an SQL class and learn how to code databases properly? Do you do anything else the worst possible way or just your database tables?
    I gues there is some history between the two of you? And if so; do you have to do that here?

  11. #11
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by vinny42 View Post
    I gues there is some history between the two of you?
    No. I was just responding to a comment that indicate that the person who made it did not really understand SQL properlly but was attempting to give advice about it even though an SQL expert had previously identified that "3) ENUM is the spawn of the devil, please avoid it fastidiously".and had then gone on to post links to pages that explain why you should NEVER use it.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  12. #12
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No. I was just responding to a comment that indicate that the person who made it did not really understand SQL properlly but was attempting to give advice about it even though an SQL expert had previously identified that "3) ENUM is the spawn of the devil, please avoid it fastidiously".and had then gone on to post links to pages that explain why you should NEVER use it.
    In that case your reaction is way out of line.

    As I discussed, politely and objectively, ENUM itself is not the spawn of the devil, it is a solution to a problem. If you have that problem, then ENUM is the solution. If you have a different problem, then ENUM is not the solution. So no, the poster you flamed is not wrong about using ENUM for yes/no situations, so don't flame him for it.

    All this is ofcourse moot when using MySQL because MySQL's ENUM realy *is* the spawn of the devil, but then again, most of MySQL smells of brimstone :-)

  13. #13
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Do you think you will ever take an SQL class and learn how to code databases properly? Do you do anything else the worst possible way or just your database tables?
    You mistake my poking fun at rudy for actual stupidity

    hint: I don't actually ever use enum.
    <cfset myblog = "http://cydewaze.org/">

  14. #14
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by cydewaze View Post
    You mistake my poking fun at rudy for actual stupidity
    .
    My apologies - I misinterpreted the smilies.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  15. #15
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Actually, since we're on the enum subject, maybe I can use the opportunity to learn some more.

    When I need a "yes/no" flag, I've always used TINYINT and a 1 or 0. I use this in cases like whether or not a post in my blog is a draft or a final, whether or not a comment is approved or not, etc.

    I guess I do this because I prefer to use numbers (it's easier to type <cfif flag eq 1> than <cfif flag eq "y"> in ColdFusion) to strings, but maybe there is a better way to do this.

    Any advice?
    <cfset myblog = "http://cydewaze.org/">

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    advice: stick to tinyint

    much easier to convert it to a foreign key than an enum

    also, i believe you can simply say <CFIF flag> and the non-zero value evaluates as true
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy!
    <cfset myblog = "http://cydewaze.org/">

  18. #18
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,156
    Mentioned
    190 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    .....
    also, i believe you can simply say <CFIF flag> and the non-zero value evaluates as true
    Do you know if it needs to be unsigned, eg. would -1 be true?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mittineague View Post
    Do you know if it needs to be unsigned, eg. would -1 be true?
    you got me... i give up... i dunno...

    does anyone have coldfusion running that could give us a quick test?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Code CFM:
    <cfset test = 0>
    <cfif test>True<cfelse>False</cfif>
     
    <cfset test = 1>
    <cfif test>True<cfelse>False</cfif>
     
    <cfset test = -1>
    <cfif test>True<cfelse>False</cfif>


    Results:

    False

    True

    True
    <cfset myblog = "http://cydewaze.org/">

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    cydewaze, you da man, thx
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •