Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question 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
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Until such time as you end up with say 90%+ users inactive there will be little point in indexing it. Until then it will be about as quick to locate the records without an index. If 90% are active it will probably be quicker without the index.

    Don't use enum - the int value is more efficient in this instance and there are ALWAYS disadvantages when using enum (although in this particular case the difference is trivial - except for if it encourages the use of enum in other situations where its use can cause all sorts of future problems).
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    phantom007 (11-21-2013)

  • #3
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    ok thanks for ur reply, still waiting for someone to answer my first point.


    thanks

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    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`),
     INDEX createtimestamp
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    Why work harder than that????

    If the table already exists, then just do
    Code:
    CREATE INDEX users_cts ON users(createtimestamp);
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (11-22-2013)

  • #5
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    Thanks for that Old Pedant

  • #6
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,861
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    1) What type of indexes do I have to set for the "createtimestamp" field since its used in the query to sort records.
    Why don't you just use the userid itself to sort them?
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    phantom007 (11-22-2013)

  • #7
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by abduraooft View Post
    Why don't you just use the userid itself to sort them?
    Will that make it faster than sorting by createtimestamp?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Quote Originally Posted by cancer10 View Post
    Will that make it faster than sorting by createtimestamp?
    No. Or, if so, maybe save 1% or so. Timestamps are, essentially, long integers.

    But I don't understand the point of "make it faster". What matters is: Which index will produce the more reasonable results?

    In your system, doing ORDER BY either userid or createtimestamp should give the same ordering.

    But isn't it more likely that you would want to view all users by fullname? If so, then you would want an index on that field.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (11-23-2013)


  •  

    Posting Permissions

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