Using an enum index to limit records

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).

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'
  
  UNION ALL
  
  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 ...)