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).
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
<= 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')
SELECT a.* FROM assessments AS a WHERE state < 'done' UNION ALL SELECT a.* FROM assessments AS a WHERE state > 'done' ;
write the condition with
WHERE state IN ('draft', 'proofing', 'amends', ...) -- everything except 'done' WHERE (state = 'draft' OR state = 'proofing' OR state = 'amends' OR ...)