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 &lt; or &lt;= 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 &lt;&gt; 'done'


Things you can do instead:


write the condition as: 

WHERE (state &lt; 'done' OR state &gt; 'done')

use UNION:

SELECT a.*
FROM assessments AS a
WHERE state &lt; 'done'

UNION ALL

SELECT a.*
FROM assessments AS a
WHERE state &gt; '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 ...)