MySQL Indexing a 'Status' field

Hi

Following is my table DDL


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

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.

  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

Thanks for the reply r937

Mind elaborating pt # 3?

Thanks

google “ENUM is the spawn of the devil”

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.

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

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 :slight_smile: )

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.

I love enum :headoverheels: I use it for all my yes/no needs :slight_smile:

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 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?

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.

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 :slight_smile:

You mistake my poking fun at rudy for actual stupidity :slight_smile:

hint: I don’t actually ever use enum.

My apologies - I misinterpreted the smilies.

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?

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

Thanks rudy! :slight_smile:

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?

<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