Using an enum index to limit records

Posted on

Question :

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

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *