A common issue, just not sure on the keywords.

I have a table of assessments (14k+ records).

Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done').

Most records (13k) are 'done'... so doing a:

WHERE state != 'done'

It should ignore most records, however MySQL ignores the index (presumably due to the low cardinality).

I could USE/FORCE INDEX, which kind of helps, but was wondering if this is the best approach.

One option is to add a datetime for when it is done, but not really used at the moment... but would have high cardinality (with NULL for those 1k records).

  • Please mention your MySQL version and the results you get when you try this: instead of what I assume you are doing, WHERE state != 'done' try the opposite and comment on what you see: WHERE STATE IN ('draft','proofing','amends') listing every other possible state except 'done'. It's a little counter-intuitive but at least in 5.6 the optimizer seems to choose the index with WHERE ... IN () but not with WHERE ... != on an ENUM. Also, explain what you mean by use/force index "kind of helps". Sep 26, 2013 at 11:56
  • Thanks @Michael-sqlbot, I've updated and your suggestion to use WHERE ... IN () seems to help (will continue to test). Sep 26, 2013 at 13:46
  • Can you add the SHOW CREATE TABLE name; output? What is the engine of the table? Sep 26, 2013 at 13:59
  • It's MyISAM at the moment (for a FULLTEXT index)... and the table structure is quite big (storing lots of text/tinytext values)... so using the INDEX is quite important... but most important is working out how to solve this problem in general, as I've had it come up a couple of times before. Sep 26, 2013 at 14:50

1 Answer 1


While similar queries with = or < or <= will consider (and depending on cardinality estimates will use) an index on (state), a query with a condition of this type will not use an index.:

WHERE state <> 'done'

Things you can do instead:

  • write the condition as:

    WHERE (state < 'done' OR state > 'done')
  • use UNION:

    SELECT a.*
    FROM assessments AS a
    WHERE state < 'done'
    SELECT a.*
    FROM assessments AS a
    WHERE state > 'done' ;
  • write the condition with IN / OR:

    WHERE state IN  ('draft', 'proofing', 'amends', ...)    -- everything except 'done'
    WHERE (state = 'draft' OR state = 'proofing' OR state = 'amends' OR ...) 
  • I was doing state != "done", which seems to be the same as <>... doing the query with an OR or UNION does not seem to have an effect... but the WHERE state IN () does defiantly help... now as I have a fairly long list of states that I don't want to hard code (more could be added), would adding a SHOW COLUMNS really be a good idea (that would defiantly add time). Sep 26, 2013 at 13:54
  • Add SHOW COLUMNS where? I don't understand what you mean. Sep 26, 2013 at 13:58
  • As in use SHOW COLUMNS to return a list of the possible enum values, then use that to build the query (dropping the "done" state)... i.e. so the multiple queries that use this don't need to hard code the possible values (which can change, and will always need to just ignore the "done" state)... I can't imagine that would be a good idea though. Sep 26, 2013 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.